SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Distinct Latest Records


Distinct Latest Records

Author
Message
Ankit Mathur-481681
Ankit Mathur-481681
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 337
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
Ken McKelvey
Ken McKelvey
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1699 Visits: 7914
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


Ankit Mathur-481681
Ankit Mathur-481681
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 337
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search