How to Get Last modified record for unique ID? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to Get Last modified record for unique ID?

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2012-07-19 : 06:08:55

Hi
Below is my table structure

Id Date List name No
1 10/10/12 sam 34
1 10/11/12 Ram 35
1 10/12/12 sam 35
3 10/10/12 raje 28
3 10/13/12 ram 34

Expected OutPut(All the records)
1 10/12/12 sam 35
3 10/13/12 ram 34

can any body help me this????

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 06:40:38

Try this?


SELECT
    [ID]
    , [Date List]
    , [Name]
    , [No]
FROM
    (
    SELECT
         *
       , ROW_NUMBER() OVER ( PARTITION BY [ID] ORDER BY [Date List] DESC) AS [SortID]
    FROM
        <TheTable>
    )
    AS s
WHERE
    [SortID] = 1

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 11:08:21

[code]
SELECT t.*
FROM table t
CROSS APPLY(
SELECT MAX([Date List])AS MaxDate
FROM table
WHERE ID = t.ID
)t1
WHERE MaxDate = t.[Date List]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 11:16:28

visakh's suggestion could give you multiple results for each partition if the dates are not unique -- mine will give a random 1....

Neither of these behaviors are particularly desirable! You can add more clauses to the ORDER BY of ROW_NUMBER however..

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Mng
Yak Posting Veteran

59 Posts

Posted - 2012-07-20 : 09:01:39

Thanks Man..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 10:17:37

quote:
Originally posted by Mng

Thanks Man..

Hope you saw what Charlie suggested. Do you've sample data to show us what exactly you were looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources