CODEWITHSUNDEEP
postgresql
Kushal Jain
Kushal Jain

Reputation: 3186

Select rows by one column value should only be repeat N times

My table is:

id  sub_id datetime    resource
---|-----|------------|-------
1  | 10  | 04/03/2009 | 399 
2  | 11  | 04/03/2009 | 244
3  | 10  | 04/03/2009 | 555
4  | 10  | 03/03/2009 | 300
5  | 11  | 03/03/2009 | 200
6  | 11  | 03/03/2009 | 500
7  | 11  | 24/12/2008 | 600
8  | 13  | 01/01/2009 | 750
9  | 10  | 01/01/2009 | 760
10 | 13  | 01/01/2009 | 570
11 | 11  | 01/01/2009 | 870
12 | 13  | 01/01/2009 | 670
13 | 13  | 01/01/2009 | 703
14 | 13  | 01/01/2009 | 705

I need to select for each sub_id only 2 times

Result would be:

id  sub_id datetime    resource
---|-----|------------|-------
1  | 10  | 04/03/2009 | 399 
3  | 10  | 04/03/2009 | 555
5  | 11  | 03/03/2009 | 200
6  | 11  | 03/03/2009 | 500
8  | 13  | 01/01/2009 | 750
10 | 13  | 01/01/2009 | 570

How can I achieve this result in postgres ?

Upvotes: 1

Views: 43

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51446

look at the order column (I use id to match your sample):

t=# with data as (select *,count(1) over (partition by sub_id order by id) from t)
select id,sub_id,datetime,resource from data where count <3;
 id | sub_id |  datetime  | resource
----+--------+------------+----------
  1 |     10 | 2009-03-04 |      399
  3 |     10 | 2009-03-04 |      555
  2 |     11 | 2009-03-04 |      244
  5 |     11 | 2009-03-03 |      200
  8 |     13 | 2009-01-01 |      750
 10 |     13 | 2009-01-01 |      570
(6 rows)

Upvotes: 1

klin
klin

Reputation: 121544

Use the window function row_number():

select id, sub_id, datetime, resource
from (
    select *, row_number() over (partition by sub_id order by id)
    from my_table
    ) s
where row_number < 3;

Upvotes: 1

Related Questions