CODEWITHSUNDEEP
sqlpostgresqlselectgreatest-n-per-groupsql-null
Allie Fitter
Allie Fitter

Reputation: 1807

Group by column with preference for rows in which another column is not null

Say I have a table which looks like this, with two foreign keys:

| col1 | fkey1 | fkey2 |
|------|-------|-------|
| foo  | 123   | null  |
| foo  | 123   | 456   |
| bar  | 789   | null  |

How would I group by col1, with a preference for the row in which fkey2 is not null? So that the result would look like this:

| col1 | fkey1 | fkey2 |
|------|-------|-------|
| foo  | 123   | 456   |
| bar  | 789   | null  |

One other consideration is that fkey1 has a not null constraint on it, while fkey2 does not.

Upvotes: 0

Views: 30

Answers (1)

GMB
GMB

Reputation: 222462

For this dataset, you could use simple aggregation:

select col1, fkey1, max(fkey2) fkey2
from mytable
group by col1, fkey1

But I suspect that you actually want distinct on:

select distinct on(col1) t.*
from mytable t
order by col1, fkey2

Upvotes: 1

Related Questions