Menu
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?
420
PostgreSQL also provides on an expression as DISTINCT ON that is used with the SELECT statement to remove duplicates from a query set result just like the DISTINCT clause.In addition to that it also keeps the “first row” of each row of duplicates in the query set result.
DISTINCT must follow SELECT. It applies to the entire tuple, not to an attribute of the result. DISTINCT ON is a postgresql addition to the language. It is similar, but not identical, to group by.
GROUP BY is intended for aggregate function use; DISTINCT just removes duplicates (based on all column values matching on a per row basis) from visibility. If TABLE2 allows duplicate values associated to TABLE1 records, you have to use either option.
The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
