SQL Server get unique rows or row with largest field value

Question

Hi,

I have a table in SQL Server 2005, as follows, say, fields A, B, C, D

If I have the following data:

A     B     C     D

1     B1    C1    D1 -
2     B1    C1    D1 -
3     B2    C2    D2 -
4     B2    C2    D2 -
5     B2    C2    D2 -
6     B3    C3    D3 -

I want to be able to pick out the rows which are either unique (on B, C and D), or, if not unique (on B, C and D), then I would like to pick up only the row with the largest value in the A field. So, I would like to return row 2 (A=2), row 5 and row 6 in the above scenario. I would not like to use cursors if possible but set based operations.

Is there any way I can accomplish this, in a way that I can use in a stored procedure or table-function?

Thanks in advance, Tim

Answer 1

That should be easy. Just group by b, c, d and use MAX to get the highest value for a.

Select Max(a), b, c, d
From your_table
Group By b, c, d

Answer 2

Assuming that you may well have additional columns to return...

with cte as
(
select a,b,c,d,
row_number() over (partition by b,c,d order by a desc) as rn
from yourtable
)
select a,b,c,d
from cte
where rn=1