Search code examples
sqlpostgresqlselectgreatest-n-per-groupwindow-functions

How to select a random record for each group

I have a table like

 |    A   | B | C | D |
 |--------|---|---|---|
 | Value1 | x | x | x |
 | Value1 | y | x | y |
 | Value1 | x | x | x |
 |        ....        |
 | Value2 | x | x | x |
 | Value2 | x | x | x |
 | Value2 | x | x | x |
 |        ....        |
 | Value3 | x | x | x |
 | Value3 | x | x | x |
 | Value3 | x | x | x |

where A column can have one value from a set. I want to get a random record for each unique value in A column.

Solution

  • You can do it with distinct on:

    select distinct on (a) a, b, c, d
from test t;

    Here is a Demo

    With DISTINCT ON, You tell PostgreSQL to return a single row for each distinct group defined by the ON clause.

    More about that subject here: https://www.geekytidbits.com/postgres-distinct-on/