CODEWITHSUNDEEP
postgresql
IoannaI
IoannaI

Reputation: 11

Postgres- select all rows matching the first 10 distinct ids

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!

Upvotes: 0

Views: 313

Answers (1)

Michal T
Michal T

Reputation: 611

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)

Upvotes: 1

Related Questions