SQL Server equivalent of PostgreSQL distinct on ()

I would like to have a Sql Server equivalent of the PostgreSQL distinct on ()

a  b
----
1  1
1  2
2  2
2  1
3  3

select distinct on (a) * 
from my_table

a  b
----
1  1
2  2
3  3

I could do in SQL Server:

开发者_StackOverflow中文版select a, min(b) -- or max it does not matter
from my_table
group by a

But in cases where there are many columns and the query is an ad hoc one it is very tedious to do. Is there an easy way to do it?

You can try ROW_NUMBER, but it can affect your performance.

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY a ORDER BY b) Corr
    FROM my_table
)
SELECT *
FROM CTE
WHERE Corr = 1

In addition to the accepted answer, you can avoid using two sentences and use a sub select like this

SELECT part.*
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY a ORDER BY b) Corr
    FROM my_table) part
WHERE part.Corr = 1