Tutorial :Grabbing most recent row based on column with repeating data?



Question:

Table [myRecords]

     A     |     B     |     C     |     D     |     E     |     F     |  ------------------------------------------------------------------------       1     |     10    |  1/1/09   |    abc    |    aaa    |    111    |       2     |     10    |  1/2/09   |    def    |    bbb    |    222    |       3     |     10    |  1/3/09   |    ghi    |    ccc    |    333    |       4     |     12    |  1/4/09   |    jkl    |    ddd    |    444    |       5     |     12    |  1/5/09   |    mno    |    eee    |    555    |       6     |     13    |  1/6/09   |    pqr    |    fff    |    666    |       7     |     13    |  1/7/09   |    stu    |    ggg    |    777    |       8     |     14    |  1/8/09   |    vwx    |    hhh    |    888    |       9     |     14    |  1/9/09   |    yza    |    iii    |    999    |       10    |     15    |  1/10/09  |    bcd    |    jjj    |    101    |  

Result should be (grabbing most recent (c) for each distinct (b).

     A     |     B     |     C     |     D     |     E     |     F     |  ------------------------------------------------------------------------       3     |     10    |  1/3/09   |    ghi    |    ccc    |    333    |       5     |     12    |  1/5/09   |    mno    |    eee    |    555    |       7     |     13    |  1/7/09   |    stu    |    ggg    |    777    |       9     |     14    |  1/9/09   |    yza    |    iii    |    999    |       10    |     15    |  1/10/09  |    bcd    |    jjj    |    101    |  


Solution:1

SELECT * FROM myRecords t1   WHERE c = (SELECT max(c) FROM myRecords t2 WHERE t2.B = t1.B)  


Solution:2

select t.*  from (      select b, max(c) as maxc      from table1      group by b  ) tm  inner join table1 t on tm.b = t.b and tm.maxc = t.c  


Solution:3

select t.* from table t join (select b, max(c) as maxc from table group by b) as t2 on t.b=t2.b and t.c=t2.maxc order by t.b


Solution:4

Assuming the date column is never NULL, I recommend RANK() if you at at at least SQL Server 2005. It's much easier to manage if you ever make changes, because you don't have to change things in the subquery and in the main query.

For SQL Server 2005 and later:

WITH myRecordsRanked(A,B,C,D,E,F,rk) AS (    SELECT A,B,C,D,E,F,      RANK() OVER (        PARTITION BY B        ORDER BY C DESC      )    FROM myRecords  )    SELECT A,B,C,D,E,F    FROM myRecordsRanked    WHERE rk = 1;  

Selecting the rk=1 values will give you ties. Use a tiebreaking ORDER BY column if you need a specific one of the tied rows, or use ROW_NUMBER if you don't need a consistent choice among ties and find it's worth avoiding additional sorting.

For SQL Server 2000:

SELECT A,B,C,D,E,F  FROM myRecords  WHERE NOT EXISTS (    SELECT * FROM myRecords as R2    WHERE R2.B = myRecords.B    AND R2.C > myRecords.C  )  

(This will also give you ties for latest date for a column B value.)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »