February 5, 2008 at 6:43 am
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
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
February 5, 2008 at 7:53 am
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
February 5, 2008 at 10:03 pm
Thanks Ken,
That worked like a charm. Perfectly according to my needs.
With this help in SQL Query you have ended my search of past few weeks to get some sort of headway with this query.
Thanks again.
Ankit Mathur 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply