Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distinct Latest Records Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2008 6:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 5, 2014 2:22 AM
Points: 167, Visits: 328
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
Post #451606
Posted Tuesday, February 5, 2008 7:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 845, Visits: 5,452
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

Post #451644
Posted Tuesday, February 5, 2008 10:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 5, 2014 2:22 AM
Points: 167, Visits: 328
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 :)
Post #452002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse