CODEWITHSUNDEEP
sqlpostgresql
savovit902
savovit902

Reputation: 25

Get only first N rows met the condition but keep rows where the condition isn't met

id type
1 body
2 body1
3 body
4 body1
5 body1
6 body1
7 body
8 body1
9 body1
10 body

Is it possible to select first 3 rows where type is body but keep other rows where type isn't equal body? Expected result is rows with id from 1 to 7.

Upvotes: 0

Views: 458

Answers (4)

Nikita Zhuikov
Nikita Zhuikov

Reputation: 1062

You need to use window function DENSE_RANK():

SELECT
*
FROM
(
    SELECT
        DENSE_RANK() OVER (PARTITION BY t1.type ORDER BY t1.id) rnk,
        t1.*
    FROM table t1
) t2
WHERE
 (t2.type = 'body' and rnk <= 3) or (t2.type != 'body')

Upvotes: 0

Ftisiot
Ftisiot

Reputation: 1868

An alternative solution is use ranking

reproducing your table with

create table test_body (id integer, str_type varchar);
insert into test_body values(1, 'body');
insert into test_body values(2, 'body1');
insert into test_body values(3, 'body');
insert into test_body values(4, 'body1');
insert into test_body values(5, 'body1');
insert into test_body values(6, 'body1');
insert into test_body values(7, 'body');
insert into test_body values(8, 'body1');
insert into test_body values(9, 'body1');
insert into test_body values(10, 'body');

The following query calculates the ranking for the condition and then uses it to filter the data

with ranking_sel as(
select *, 
case when str_type = 'body' then 1 else 0 end condition,
rank() over (partition by case when str_type = 'body' then 1 else 0 end order by id) rnk from test_body 
)

select * from ranking_sel where rnk <= 3 or condition = 0 order by id;

Upvotes: 0

JayTheKay
JayTheKay

Reputation: 1483

Is this what you are looking for?

SELECT * FROM <table> WHERE type = 'body' ORDER BY id LIMIT 3

ORDER BY will make sure you select the top rows ordered by the id column

LIMIT 3 will pick only the first 3 results

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You seem to want everything up to the third body. One method is:

select t.*
from t
where t.id <= (select t2.id
               from t t2
               where t2.type = 'body'
               order by t2.id
               limit 1 offset 2
              );

Note: This particular formulation assumes that there are at least three 'body' rows. It can be tweaked but this is consistent with your sample data.

Upvotes: 1

Related Questions