Select N Rows per Client - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select N Rows per Client

Author

Topic

Getzin
Starting Member

6 Posts

Posted - 2009-09-09 : 07:27:45

I have a table with following columns:
ClientCode, ChangeDateTime, OldPassword.

As passwords are changed, the oldpassword and datetime as saved in the table. I need a select stmt to get the last N records per client.
Or a delete stmt which keeps the last N records per client and deletes rest.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-09 : 07:40:30

select top <N> * from table where ClientCode= <ClientCode>
order by ChangeDateTime

OR

Delete From table where not in(select top <N> * from table where ClientCode= <ClientCode>
order by ChangeDateTime)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Getzin
Starting Member

6 Posts

Posted - 2009-09-09 : 07:47:33

Hi Senthil,
Thank for your answer, but it won't satisfy my requirement.
I want to keep last N records for every Client, and drop the rest.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-09 : 08:20:12

see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-09 : 10:31:51

DECLARE @tab TABLE (ClientCode INT , ChangeDateTime datetime, OldPassword VARCHAR(10))

INSERT INTO @tab
SELECT 1, getdate(), 'abc1'
UNION ALL
SELECT 1, getdate()-2, 'abc2'
UNION ALL
SELECT 1, getdate()-3, 'abc3'
UNION ALL
SELECT 1, getdate()-4, 'abc4'
UNION ALL
SELECT 2, getdate(), 'abc1'
UNION ALL
SELECT 2, getdate()-2, 'abc2'
UNION ALL
SELECT 2, getdate()-3, 'abc3'
UNION ALL
SELECT 2, getdate()-4, 'abc4'
UNION ALL
SELECT 3, getdate(), 'abc1'
UNION ALL
SELECT 3, getdate()-2, 'abc2'
UNION ALL
SELECT 3, getdate()-3, 'abc3'
UNION ALL
SELECT 3, getdate()-4, 'abc4'

SELECT * FROM
(
SELECT row_number() OVER (partition BY ClientCode ORDER BY ChangeDateTime DESC) RN, * FROM @tab
)a
WHERE RN < 3

Rahul Shinde

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 02:36:42

quote:
Originally posted by ra.shinde

DECLARE @tab TABLE (ClientCode INT , ChangeDateTime datetime, OldPassword VARCHAR(10))

INSERT INTO @tab
SELECT 1, getdate(), 'abc1'
UNION ALL
SELECT 1, getdate()-2, 'abc2'
UNION ALL
SELECT 1, getdate()-3, 'abc3'
UNION ALL
SELECT 1, getdate()-4, 'abc4'
UNION ALL
SELECT 2, getdate(), 'abc1'
UNION ALL
SELECT 2, getdate()-2, 'abc2'
UNION ALL
SELECT 2, getdate()-3, 'abc3'
UNION ALL
SELECT 2, getdate()-4, 'abc4'
UNION ALL
SELECT 3, getdate(), 'abc1'
UNION ALL
SELECT 3, getdate()-2, 'abc2'
UNION ALL
SELECT 3, getdate()-3, 'abc3'
UNION ALL
SELECT 3, getdate()-4, 'abc4'

SELECT * FROM
(
SELECT row_number() OVER (partition BY ClientCode ORDER BY ChangeDateTime DESC) RN, * FROM @tab
)a
WHERE RN < 3

Rahul Shinde