Home Get one row per unique column value combination (`DISTINCT ON` operation without using it)

Questions

Get one row per unique column value combination (`DISTINCT ON` operation without using it)

December 21, 2022

I have a table with 5 columns, For each unique combination of the first three columns, I want a single sample row. I don’t care which values are considered for columns 4 and 5, as long as they come from the same row (they should be aligned).

I realise I can do a DISTINCT to fetch on the first three columns to fetch unique combinations of the first three columns. But the problems is then I cannot get 4th and 5th column values.

I considered GROUP BY, I can do a group by on the first three columns, then I can do a MIN(col4), MIN(col5). But there is no guarantee that values of col4 and col5 are aligned (from the same row).

The DB I am using does not support DISTINCT ON operation, which I realise is what I really need.

How do I perform what DISTINCT ON does without actually using that operation ?

I am guessing this is how I would write the SQL if DISTINCT ON was supported:

SELECT
    DISTINCT ON (col1, col2, col3)
    col1, col2, col3, col4, col5
FROM TABLE_NAME

>Solution :

select
    col1, col2, col3, col4, col5
from (
    select col1, col2, col3, col4, col5,
        row_number() over (partition by col1, col2, col3) as n
    from table_name
)
where n = 1