Dave Qorashi Dave Qorashi - 7 months ago 20
SQL Question

keeping order while doing distinct

In Postgres, given having the following data:

-[ RECORD 1 ]-
id | 57425
filter | 1
-[ RECORD 2 ]-
id | 57416
filter | 1
-[ RECORD 3 ]-
id | 57426
filter | 1
-[ RECORD 4 ]-
id | 57416
filter | 2
-[ RECORD 5 ]-
id | 57424
filter | 2
-[ RECORD 6 ]-
id | 57425
filter | 2
-[ RECORD 7 ]-
id | 57427
filter | 2


What's the SQL for creating the following (removing duplicated). I don't care if it's the exact same order, I just want records with filter value '1' appear before records with filter value '2'.

-[ RECORD 1 ]-
id | 57425
filter | 1
-[ RECORD 2 ]-
id | 57416
filter | 1
-[ RECORD 3 ]-
id | 57426
filter | 1
-[ RECORD 4 ]-
id | 57424
filter | 2
-[ RECORD 5 ]-
id | 57427
filter | 2

Answer

As I suggested in a comment, this seems like a straightforward grouping/ordering problem:

SELECT id,MIN(filter) as lowFilter
FROM UnnamedTable
GROUP BY id
ORDER BY lowFilter

As soon as you find yourself saying "I want to do distinct on some of the columns ..." or "I want to do distinct but also include extra columns", you should realise that what you're in fact looking for is not DISTINCT but GROUP BY. It's then up to you to correctly formulate which aggregate(s) you want to apply to the additional columns, considering that there may be multiple values (from multiple rows) to choose between.