CODEWITHSUNDEEP
sqlpostgresql
Zaharskyy
Zaharskyy

Reputation: 119

sql - get rows with first x field values

I would like to get rows with, lets say, 3 first values for type field. For the following table called items

id name type
1 banana fruit
2 mango fruit
3 car toy
4 lion animal
5 badger animal
6 cupboard furniture
7 shirt cloth

The result would be rows 1-5 (fruit, toy, animal).

I understand using sql limit like

select * from items
limit 3;

will not return rows with animals which I want to get. Is there any smooth way to achieve that?

Upvotes: 0

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Use dense_rank() after calculating the minimum id for each type:

select t.*
from (select t.*,
             dense_rank() over (order by min_type_id) as seqnum
      from (select t.*, min(id) over (partition by type) as min_type_id
            from t
           ) t
     ) t
where seqnum <= 3;

You could also use:

select t.*
from t
where t.type in (select t2.type
                 from t t2
                 group by t2.type
                 order by min(t2.id) asc
                 limit 3
                );

Upvotes: 1

forpas
forpas

Reputation: 164089

Use LAG() and SUM() window functions to assign groups to the types and select top 3 groups:

SELECT id, name, type
FROM (
  SELECT *, SUM(flag) OVER (ORDER BY id) grp
  FROM (
    SELECT *, (type <> LAG(type, 1, '') OVER (ORDER BY id))::int flag
    FROM items
  ) t
) t
WHERE grp <= 3

See the demo.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

You can do:

select *
from items
where type in (
  select type 
  from (select type, min(id) as min_id from items group by type) x
  order by min_id
  limit 3  
)

Result:

 id  name    type   
 --- ------- ------ 
 1   banana  fruit  
 2   mango   fruit  
 3   car     toy    
 4   lion    animal 
 5   badger  animal

See running example at DB Fiddle.

Upvotes: 0

Related Questions