SQL Server equivalent of PostgreSQL distinct on ()

Question

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:

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?

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Michael Freidgeim, Sep 28 '19 at 14:45
@MichaelFreidgeim that answer only demonstrates that CTE is semantic replacement for `DISTINCT ON` but CTE stands far behind from Postgres extension in land of performance. — gavenkoa, May 03 '23 at 21:21

score 24 · Accepted Answer · answered Apr 27 '11 at 18:45

24

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

answered Apr 27 '11 at 18:45

Lamak

how to do this if I don't have the option to use with CTE?? – Nouman Bhatti Jun 01 '17 at 02:32
1

@NoumanBhatti then you move parentheses between `FROM` and `CTE`. – Radim Bača Jun 26 '18 at 09:53

score 22 · Answer 2 · answered Apr 23 '18 at 11:42

22

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

answered Apr 23 '18 at 11:42

2 Answers2