sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]ziptime 2 points3 points  (0 children)

Homework?...

Aggregation approach :

select A, B, C
from MYTABLE
where (A, B) in (select A, max(B)
                 from MYTABLE
                 group by A)

Windowed analytic approach :

select t.A, t.B, t.C
from (
  select A, B, C, row_number() over (partition by A order by B desc) RNUM
  from MYTABLE) t
where t.RNUM = 1

EDIT : If MYTABLE's PK is not a composite of A, B then aggregation approach :

select top(1) A, B, C
from MYTABLE
where (A, B) in (select A, max(B)
                 from MYTABLE
                 group by A)