SQL Server Performance Forum – Threads Archive

last row in all categories

can anybody help me? i have something like this id rank qty
—- —- —
1 1 10
1 2 5
1 3 8
2 1 16
2 2 9
3 1 11
3 2 20
3 3 42
4 1 19 how can i get the qty for last rank of each id. the result should be like this id rank qty
—- —- —
1 3 8
2 2 9
3 3 42
4 1 19

---

try select id, rank, qty from MyTable as BaseTable where
rank = (select MAX(rank) from MyTable as InnerTable where BaseTable.id = InnerTable.id)
order by id, rank

---

]]>