postgresql - Postgres- select all rows matching the first 10 distinct ids

Question

have a happy new year!

I'm looking to keep all rows in my table for the first 10 distinct IDS, not just the first 10 rows order by id.

I don't know how to though. Your input will be of great help!

Answer 1

SELECT * FROM test_id;

 id 
----
  1
  3
  5
  7
  9
 11
 13
 15
 17
 19
 21
 23
 25
 27
 29
 31
 33
 35
(18 rows)

WITH ranked_ids AS (
    select *, rank() over(order by id) AS rank from test_id
)
select * from ranked_ids WHERE rank <= 10;

 id | rank 
----+------
  1 |    1
  3 |    2
  5 |    3
  7 |    4
  9 |    5
 11 |    6
 13 |    7
 15 |    8
 17 |    9
 19 |   10
(10 rows)