SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to Get Last modified record for unique ID?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mng
Yak Posting Veteran

59 Posts
Posted - 07/19/2012 :  06:08:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3425 Posts
Posted - 07/19/2012 :  06:40:38  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Edited by - Transact Charlie on 07/19/2012 06:40:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47035 Posts
Posted - 07/19/2012 :  11:08:21  Show Profile  Reply with Quote 

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]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3425 Posts
Posted - 07/19/2012 :  11:16:28  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

Mng
Yak Posting Veteran

59 Posts
Posted - 07/20/2012 :  09:01:39  Show Profile  Reply with Quote
Thanks Man..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47035 Posts
Posted - 07/20/2012 :  10:17:37  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000