PostgreSQL Aggregate – How to Get First Value from List

aggregatepostgresql

I have data as follows

 Id   Col1   Col2
 ================
 101  101    abc
 101  102    def
 102  102    ght
 103  101    mgj
 103  102    dkf
 103  103    dfj

I need output as follows

 Id   Col1   Col2
 ================
 101  101    abc
 102  102    ght
 103  101    mgj

(by default, it should take 101 (col1), if not there 102(col1), or else 103(col1))

Any Idea?

Best Answer

You can use the DISTINCT ON of SELECT as in the documentation

SELECT DISTINCT ON (id)
  id, col1, col2
FROM TABLE
ORDER BY id, col1, col2

The difference between SELECT DISTINCT <columns> and SELECT DISTINCT ON (<columns>) <columns> is that the first gives you unique rows across all selected columns .. the second gives you one unique row per column set defined within the parenthesis, but allows for additional columns to be returned.

To specify WHICH additional data is returned, you need to ORDER BY.. DISTINCT will then return the first row for each DISTINCT ON set of columns.

Related Question