locked
How do I get the latest records based on date? RRS feed

  • Question

  • Can someone help me to retrieve the final resultset:

    Table 1
    ID     ID2     Date              Col1
    1     1         1 Jan 2009     B
    1     2         2 Jan 2009     A
    2     3         1 Jan 2009     A
    2     4         1 Jan 2009     B
    3     5         1 Jan 2009     B
    3     6         2 Jan 2009     B
    3     7         3 Jan 2009     A
    4     8         1 Jan 2009     A

    Final Resultset
    ID     ID2     Date              Col1
    1     2         2 Jan 2009     A
    2     4         1 Jan 2009     B
    3     7         3 Jan 2009     A
    4     8         1 Jan 2009     A

    Thanks.

    Wednesday, January 14, 2009 4:23 AM

Answers

  • Here is another way to do this using cross apply:

    declare @Table1 table(ID int,ID2 int,Date datetime, Col1 varchar(5))

    insert into @Table1
    select 1,1,'1 Jan 2009','B'
    union all select 1,2,'2 Jan 2009','A'
    union all select 2,3,'1 Jan 2009','A'
    union all select 2,4,'1 Jan 2009','B'
    union all select 3,5,'1 Jan 2009','B'
    union all select 3,6,'2 Jan 2009','B'
    union all select 3,7,'3 Jan 2009','A'
    union all select 4,8,'1 Jan 2009','A'

    select distinct t2.* from @Table1 t1 cross apply
    (select top 1 * from @Table1 where id=t1.id order by date desc) t2


    mobin
    • Marked as answer by jingzo Wednesday, January 14, 2009 10:43 PM
    Wednesday, January 14, 2009 5:13 AM
  • here is another way for sql server 2000

    select t1.* from @Table1 t1 join
    (select distinct (select top 1 ID2 from @Table1 where id=t2.id order by date desc) ID2 from @Table1 t2 ) t3 on t1.id2=t3.id2

     


    mobin
    • Marked as answer by jingzo Wednesday, January 14, 2009 10:44 PM
    Wednesday, January 14, 2009 6:43 AM

All replies

  • Hi Jingzo,

    So you need the record corresponding to the MAX(Date) for each ID value?

    Something like this should work (on SQL 2005 or 2008):

    ;WITH NumberedRecs (ID, ID2, Date, Col1, RowNumber)  
    AS 
    (SELECT ID, ID2, Date, Col1, Row_Number() OVER (PARTITION BY ID ORDER BY [DateDESCAS RowNumber  
    FROM Table1)  
     
    SELECT   *   
    FROM     Table1  
    WHERE    RowNumber = 1  
    ORDER BY [ID] ASC 

    It numbers the rows based upon descending [Date], and then excludes rows with RowNumber > 1.

    Does this help?
    Aaron Alton | thehobt.blogspot.com
    Wednesday, January 14, 2009 4:42 AM
  • Here is another way to do this using cross apply:

    declare @Table1 table(ID int,ID2 int,Date datetime, Col1 varchar(5))

    insert into @Table1
    select 1,1,'1 Jan 2009','B'
    union all select 1,2,'2 Jan 2009','A'
    union all select 2,3,'1 Jan 2009','A'
    union all select 2,4,'1 Jan 2009','B'
    union all select 3,5,'1 Jan 2009','B'
    union all select 3,6,'2 Jan 2009','B'
    union all select 3,7,'3 Jan 2009','A'
    union all select 4,8,'1 Jan 2009','A'

    select distinct t2.* from @Table1 t1 cross apply
    (select top 1 * from @Table1 where id=t1.id order by date desc) t2


    mobin
    • Marked as answer by jingzo Wednesday, January 14, 2009 10:43 PM
    Wednesday, January 14, 2009 5:13 AM
  • OK - SQL 2000 doesn't have ROW_NUMBER or CTE's.  This should work though:

    SELECT  t1.*  
    FROM    Table1 AS t1  
    JOIN    (SELECT     [ID], max([Date]) AS MaxDate  
            FROM        Table1  
            GROUP BY    ID) AS maxdates ON t1.ID = maxdates.ID AND t1.[Date] = maxdates.MaxDate 

    Does this do the trick?
    Aaron Alton | thehobt.blogspot.com
    Wednesday, January 14, 2009 6:30 AM
  •   The code does not work. 
    ;WITH NumberedRecs (ID, ID2, Date, Col1, RowNumber)  
    AS 
    (SELECT ID, ID2, Date, Col1, Row_Number() OVER (PARTITION BY ID ORDER BY [DateDESCAS RowNumber  
    FROM Table1)  
     
    SELECT   *   
    FROM     Table1  
    WHERE    RowNumber = 1  
    ORDER BY [ID] ASC 

    It give "Invalid column name 'RowNumber'."

    ---

    select distinct t2.* from @Table1 t1 cross apply
    (select top 1 * from @Table1 where id=t1.id order by date desc) t2

    This works but does it support sql server 2000?
    Wednesday, January 14, 2009 6:31 AM
  •  
    SELECT  t1.*  
    FROM    Table1 AS t1  
    JOIN    (SELECT     [ID], max([Date]) AS MaxDate  
            FROM        Table1  
            GROUP BY    ID) AS maxdates ON t1.ID = maxdates.ID AND t1.[Date] = maxdates.MaxDate 

    Result:
    4 8 2009-01-01 A
    3 7 2009-01-03 A
    2 3 2009-01-01 A
    2 4 2009-01-01 B
    1 2 2009-01-02 A

    This won't give me the result. It give 2 record for id = 2


    Wednesday, January 14, 2009 6:40 AM
  • here is another way for sql server 2000

    select t1.* from @Table1 t1 join
    (select distinct (select top 1 ID2 from @Table1 where id=t2.id order by date desc) ID2 from @Table1 t2 ) t3 on t1.id2=t3.id2

     


    mobin
    • Marked as answer by jingzo Wednesday, January 14, 2009 10:44 PM
    Wednesday, January 14, 2009 6:43 AM
  •  
    jingzo said:
    This won't give me the result. It give 2 record for id = 2



    Which one of those two records do you wish to return and why?
    George
    Wednesday, January 14, 2009 9:51 AM
    Answerer
  • Extending Aarons approach further
    SELECT t.*  
    FROM   @t As [t]  
     INNER 
      JOIN (  
            SELECT t.id  
                 , t.date As [max_date]  
                 , Max(t.id2) As [max_id2]  
            FROM   @t As [t]  
             INNER 
              JOIN (  
                    SELECT id  
                         , Max([date]) As [max_date]  
                    FROM   @t  
                    GROUP 
                        BY id  
                   ) As [x]  
                ON t.id = x.id  
               AND t.date = x.max_date  
            GROUP 
                BY t.id  
                 , t.date 
           ) As [y]  
        ON t.id = y.id  
       AND t.id2 = y.max_id2  
       AND t.date = y.max_date 

    George
    Wednesday, January 14, 2009 9:54 AM
    Answerer