none
Latest Records depending on Last Modified Date

    Question

  • Hi there,

                I have to get the latest records from the table depending on Last Modified date.

    Table:

    ID  Function   Apply        Approved    Withdrawn   LastModifiedDate
    1    Coding    1/1/2010   15/1/2010   21/1/2010     21/1/2010 3:58:23
    1    Coding    1/1/2010    NULL           NULL           21/1/2010 4:58:23
    2    Testing    1/1/2010   15/1/2010   21/1/2010    21/1/2010 3:58:23
    3    Analysis  1/1/2010    NULL           NULL           21/1/2010 4:58:23

     

    Output is needed on the basis of last modified date if same fucntion
    comes more than 1 time. else all other functions need which appears once in table.

    ID  Function  Apply      Approved    Withdrawn        LastModifiedDate
    1    Coding   1/1/2010     NULL           NULL            21/1/2010 4:58:23
    2    Testing   1/1/2010   15/1/2010   21/1/2010      21/1/2010 3:58:23
    3    Analysis 1/1/2010     NULL            NULL           21/1/2010 4:58:23

    Thanks,

    Sadaf

     

    Thursday, August 26, 2010 5:10 AM

Answers

  • Take a look for the solutions and explanations of this problem:

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 27, 2010 9:36 PM
    Moderator
  • select *
    from MyTable t where not exists (select * from MyTable where id=t.id and LastModifiedDate>t.LastModifiedDate )
    
    

    http://www.t-sql.ru
    • Marked as answer by Ai-hua Qiu Wednesday, September 1, 2010 9:19 AM
    Thursday, August 26, 2010 5:43 AM
  • Hi,

    Which version of SQL Server are you using? If you are using SQL Server 2005 or above, please refer to the following sample:

    DECLARE @T TABLE

    (

     ID INT,

     Fuction NVARCHAR(20),

     [Apply]  NVARCHAR(20),

     Approved NVARCHAR(20),

     Withdrawn NVARCHAR(20),

     LastModifiedDate DATETIME

    )

     

    INSERT INTO @T VALUES(1,'Coding','2010-01-01','2010-01-15','2010-01-21','2010-01-21 3:58:23')

    INSERT INTO @T VALUES(1,'Coding','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

    INSERT INTO @T VALUES(2,'Testing','2010-01-01',' 2010-01-15','2010-01-21','2010-01-21 3:58:23')

    INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 1:58:23')

    INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

     

     

    ;WITH CTE

    AS

    (

    SELECT *, ROW_NUMBER() OVER (PARTITION  BY ID ORDER BY LastModifiedDate DESC )AS OrderNO FROM @T

     

    )

     

    SELECT * FROM CTE WHERE OrderNO=1

     

    For more information, please see:

    Ranking Functions (Transact-SQL)

     

    Using Common Table Expressions

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    • Proposed as answer by Atif-ullah Sheikh Monday, August 30, 2010 4:11 AM
    • Marked as answer by Ai-hua Qiu Wednesday, September 1, 2010 9:20 AM
    Monday, August 30, 2010 1:55 AM

All replies

  • select top 1 with ties *
    from MyTable 
    order by row_number() over (partition by id order by LastModifiedDate desc)
    

    http://www.t-sql.ru
    Thursday, August 26, 2010 5:19 AM
  • Thanks Alexey,

               Solution is giving me only one row, could u plz explain order by clause.

     

    Thursday, August 26, 2010 5:32 AM
  • select *
    from MyTable t where not exists (select * from MyTable where id=t.id and LastModifiedDate>t.LastModifiedDate )
    
    

    http://www.t-sql.ru
    • Marked as answer by Ai-hua Qiu Wednesday, September 1, 2010 9:19 AM
    Thursday, August 26, 2010 5:43 AM
  • Take a look for the solutions and explanations of this problem:

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 27, 2010 9:36 PM
    Moderator
  • Hi, 

     

    Select ID, Function, Apply, Approved, Withdrawn, LastModifiedDate From Table1 Where LastModifiedDate = (Select Max(SubQry.LastModifiedDate) From Table1 As SubQry Where Table1.ID = SubQry.ID And Table1.Function = SubQry.Function And Table1.Apply = SubQry.Apply And Table1.Approved = SubQry.Approved And Table1.Withdrawn = SubQry.Withdrawn)

     


    Best Wishes, Arbi Please vote if you find the posting was helpful.
    Friday, August 27, 2010 10:15 PM
  • Hi,

    Which version of SQL Server are you using? If you are using SQL Server 2005 or above, please refer to the following sample:

    DECLARE @T TABLE

    (

     ID INT,

     Fuction NVARCHAR(20),

     [Apply]  NVARCHAR(20),

     Approved NVARCHAR(20),

     Withdrawn NVARCHAR(20),

     LastModifiedDate DATETIME

    )

     

    INSERT INTO @T VALUES(1,'Coding','2010-01-01','2010-01-15','2010-01-21','2010-01-21 3:58:23')

    INSERT INTO @T VALUES(1,'Coding','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

    INSERT INTO @T VALUES(2,'Testing','2010-01-01',' 2010-01-15','2010-01-21','2010-01-21 3:58:23')

    INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 1:58:23')

    INSERT INTO @T VALUES(3,'Analysis','2010-01-01',NULL,NULL,'2010-01-21 4:58:23')

     

     

    ;WITH CTE

    AS

    (

    SELECT *, ROW_NUMBER() OVER (PARTITION  BY ID ORDER BY LastModifiedDate DESC )AS OrderNO FROM @T

     

    )

     

    SELECT * FROM CTE WHERE OrderNO=1

     

    For more information, please see:

    Ranking Functions (Transact-SQL)

     

    Using Common Table Expressions

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    • Proposed as answer by Atif-ullah Sheikh Monday, August 30, 2010 4:11 AM
    • Marked as answer by Ai-hua Qiu Wednesday, September 1, 2010 9:20 AM
    Monday, August 30, 2010 1:55 AM