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    |

Answer 1

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

Answer 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

Answer 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

Answer 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.)