Sql Two Criteria From One Group-by

I have a table with some 'functionally duplicate' records - different IDs, but the 4 columns of 'user data' (of even more columns) are identical. I've got a query working that will

Solution 1:

This can be radically simpler:

SELECTDISTINCTON (b, c, e, f)
       b, c, e, f, id   -- add more columns freely
FROM   (<duplicate query here>) subORDERBY b, c, e, f, (a ISNOT NULL), id

• Your duplicate query has all columns. No need to JOIN to the base table again.

• Use the Postgres extension of the standard SQL DISTINCT: DISTINCT ON:

  • Select first row in each GROUP BY group?

• Postgres has a proper boolean type. You can ORDER BY boolean expression directly. The sequence is FALSE (0), TRUE (1), NULL (NULL). If a is NULL, this expression is FALSE and sorts first: (a IS NOT NULL). The rest is ordered by id. Voilá.

• Selection of ID happens automatically. According to your description you want the ID of the row selected in this query. Nothing more to do.

• You can probably integrate this into your duplicate query directly.