Distinct Latest Records

[ankitmathur]

Hi Friends,



I've been looking for an answer to this problem for quite sometime but am unable to find one. I hope someone can help me guide to the right way.

 
SQL Query:
 
SELECT AcctID, AmtAfter, RenewDate FROM Usr_AmtDetails Order By AcctId
 
 
 
Data Retrieved by the Query:
 
 
AcctId		 |	 AmtAfter		|		 RenewDate
 
000280196 | 2006.1202 | 2007-02-09 17:35:22.250
000280196 | 4006.1202 | 2007-02-09 17:46:59.623
002101839 | 1454.0000 | 2007-07-11 00:09:41.857
002107518 | 1012.9197 | 2007-06-14 15:00:34.013
002107518 | 1001.2396 | 2007-07-26 20:26:36.483
002107518 | 1000.5498 | 2007-02-13 12:18:55.233
002107582 | 1590.0214 | 2007-03-15 14:58:55.327
002107582 | 590.0214 | 2007-03-15 15:04:30.090
002111130 | 2531.1910 | 2007-03-19 17:37:46.577
002111130 | 2675.6800 | 2006-12-15 10:46:59.750
002111130 | 2704.5999 | 2007-01-25 17:15:09.360
 
 
Data I want:
 
 
AcctId		 |	 AmtAfter		|		 RenewDate
 
000280196 | 2006.1202 | 2007-02-09 17:35:22.250
002101839 | 1454.0000 | 2007-07-11 00:09:41.857
002107518 | 1012.9197 | 2007-06-14 15:00:34.013
002107582 | 1590.0214 | 2007-03-15 14:58:55.327
002111130 | 2531.1910 | 2007-03-19 17:37:46.577

All Columns of the table: Usr_AmtDetails



Id, AcctId, RenewDate, AmtBefore, AmtAfter, RenewAmt, RenewType, RenewBy


I have MSSQL 2000


Basically, I want only the latest entry of distinct accountids & not the rest of the records also. To put it again, I want only the first entry I see of an Account for all AccountIds. If I encounter another entry of the same account I don't want it in my resultset.

Can someone guide me to a solution.



Thanks

Ankit Mathur

[OuTCasT]

Hi Friends,



I've been looking for an answer to this problem for quite sometime but am unable to find one. I hope someone can help me guide to the right way.

 
SQL Query:
 
SELECT AcctID, AmtAfter, RenewDate FROM Usr_AmtDetails Order By AcctId
 
 
 
Data Retrieved by the Query:
 
 
AcctId		 |	 AmtAfter		|		 RenewDate
 
000280196 | 2006.1202 | 2007-02-09 17:35:22.250
000280196 | 4006.1202 | 2007-02-09 17:46:59.623
002101839 | 1454.0000 | 2007-07-11 00:09:41.857
002107518 | 1012.9197 | 2007-06-14 15:00:34.013
002107518 | 1001.2396 | 2007-07-26 20:26:36.483
002107518 | 1000.5498 | 2007-02-13 12:18:55.233
002107582 | 1590.0214 | 2007-03-15 14:58:55.327
002107582 | 590.0214 | 2007-03-15 15:04:30.090
002111130 | 2531.1910 | 2007-03-19 17:37:46.577
002111130 | 2675.6800 | 2006-12-15 10:46:59.750
002111130 | 2704.5999 | 2007-01-25 17:15:09.360
 
 
Data I want:
 
 
AcctId		 |	 AmtAfter		|		 RenewDate
 
000280196 | 2006.1202 | 2007-02-09 17:35:22.250
002101839 | 1454.0000 | 2007-07-11 00:09:41.857
002107518 | 1012.9197 | 2007-06-14 15:00:34.013
002107582 | 1590.0214 | 2007-03-15 14:58:55.327
002111130 | 2531.1910 | 2007-03-19 17:37:46.577

All Columns of the table: Usr_AmtDetails



Id, AcctId, RenewDate, AmtBefore, AmtAfter, RenewAmt, RenewType, RenewBy


I have MSSQL 2000


Basically, I want only the latest entry of distinct accountids & not the rest of the records also. To put it again, I want only the first entry I see of an Account for all AccountIds. If I encounter another entry of the same account I don't want it in my resultset.

Can someone guide me to a solution.



Thanks

Ankit Mathur

have u used a identity column in your database?
You can use @@identity or @scope_identity ()
or @ident_current( )

[ck9663]

if your renewdate is unique per AcctID try this...


select summary.acctid, detail.amtafter , summary.renewda te
(SELECT AcctID, AmtAfter, RenewDate FROM Usr_AmtDetails) detail
inner join (SELECT AcctID, min(RenewDate) as renewdate FROM Usr_AmtDetails ghroup By AcctId) summary on detail.acctid = summary.acctid and detail.renewdat e = summary.renewda te

i have seen a number of shorter solutions here, so you might want to check around first...

-- ck

[ankitmathur]

if your renewdate is unique per AcctID try this...


select summary.acctid, detail.amtafter , summary.renewda te
(SELECT AcctID, AmtAfter, RenewDate FROM Usr_AmtDetails) detail
inner join (SELECT AcctID, min(RenewDate) as renewdate FROM Usr_AmtDetails ghroup By AcctId) summary on detail.acctid = summary.acctid and detail.renewdat e = summary.renewda te

i have seen a number of shorter solutions here, so you might want to check around first...

-- ck

Hi CK,

Thanks for your input I got a fair idea of how to go about it.

Here's pretty much a same query as yours that's working perfectly fine.

 
SELECT U.*FROM Usr_AmtDetails U 
JOIN (
		 SELECT X.AcctID, MAX(X.RenewDate) AS RenewDate		FROM Usr_AmtDetails X GROUP BY X.AcctID
		 ) D ON U.AcctID = D.AcctID AND U.RenewDate = D.RenewDate

Thanks for your inputs.

Ankit Mathur