Select random rows such that combination of a subgroup of columns is unique

From my_table with columns a,b,c,d and e I want to select N rows randomly such that the combinations of columns a,b and c are unique.

I tried solving this with a group by:

select a, b, c, min(d), min(e) from my_table
group by a, b, c

This approach cannot work because it does not return actual existing rows from my_table since the minimum can be in different rows for the columns d and e. Furthermore min is not an appropriate function because it does not return random rows.

How could I achieve it?

I am working with amazon athena which is built on Presto and runs standard ANSI SQL.

You didn't tell which DBMS you are working with, so it's not possible to give a complete solution.

But the strategy would be to

1. Use windowing functions for grouping rows having the same a, b, c. In Oracle this would be something like ``SELECT a, b, c, (row_number() over (partition by a, b, c order by a, b, c)) id_row
2. Use another select that picks rows from the first select, using a random() function -- but you have to look at your DBMS documentation to find out which function to use

I do not have Presto installed, only Trino (but Athena engine v3 should be based on Trino) and in Trino you can try using min_by/max_by functions combining them with random for "ordering" and some ROW magic:

select a, b, c, 
    min_by((d, e), random()).* as (d, e)
from my_table
group by a, b, c

Another approach which should work with v2 and v3 engines is using row_number window function over partition and again use random for ordering:

select a, b, c, d, e 
from(
    select *,
       row_number() over(partition by a, b, c order by random()) rn
    from dataset)
where rn = 1