Distinct Latest Records

[ankitmathur]

Hi Friends,



I've been looking for an answer to this problem for quite sometime but an 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

[cridley]

Hi there, I'm stumped on this one, just tried various methods, I can't see how this can be done in one query.

I would give up at this stage and do a loop to query each separately in whichever programming language.

results = SELECT DISTINCT ID FROM TABLE

for each (record in results)
{
result = "SELECT TOP 1 * FROM TABLE WHERE ID = record [ID] Order by Date DESC"

Display_result //or store to array or whatever
}

I'd like to know if someone can figure out the query you're after though..

[yamaharuss]

You need to add a Group By clause to your acctID field

[ankitmathur]

Hi Cridley,

Finally the problem has been solved by a friend of mine.

For your reference see the code below.



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



This seems to be working well.

Ankit Mathur