Limited By Value Postgresql query

Question

I have a couple of rows where values in one column are repeating and I need to get a couple of rows where every value is limited by const.

For example, i have this rows (1, 'a') (2, 'b') (3, 'a') (4,'c') (5, 'b') (6, 'a') and i limited every value in select by 2. Then I should not get a row with ID 6 cause this is an extra row cause I limited them by 2.

How I can do that? thx for any help

Answer 1

If you have just two columns, say id and val, and you want just one row per value, then aggregation is enough:

select min(id) as id, val
from mytable
group by val

If there are more columns, you can use distinct on:

select distinct on (val) t.*
from mytable
order by val, id

Finally, if you want to be able to allow a variable number of rows per val, you can use window functions. Say you want 3 rows maximum per value:

select *
from (
    select t.*, row_number() over(partition by val order by id) rn
    from mytable t
) t
where rn <= 3