CODEWITHSUNDEEP
sqlpostgresql
sks
sks

Reputation: 934

SQL select with dynamic counts

Suppose I have a table foo from which I'd like to select N rows from each category

create table foo(name text, category text);

 name | category 
------+----------
 aaa  | cat1
 bbb  | cat1
 ccc  | cat2
 ddd  | cat2
 eee  | cat2

Now I'd like to pick from foo given a number of categories

 category | count 
----------+-------
 cat2     |     2
 cat1     |     1

The result should be 3 rows (ties could be resolved randomly or by any order)

 name
------
 ccc
 ddd
 aaa

-- wrong, not taking counts into account
with t as (select * from (values ('cat1', 1), ('cat2', 2)) as 
t(category, count)) select name from foo, t where foo.category = t.category;

 name 
------
 aaa
 bbb
 ccc
 ddd
 eee

I could do this with multiple queries, but I think I am missing something very obvious and the select can be done with a single query.

Upvotes: 1

Views: 72

Answers (2)

krokodilko
krokodilko

Reputation: 36127

SELECT x.*
FROM categories c,
LATERAL (
  select * FROM foo
  where foo.category = c.category 
  ORDER BY random()
  LIMIT c.count
) x

Demo: http://sqlfiddle.com/#!17/e0f14/7

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You would use row_number():

select foo.*
from (select t.*,
             row_number() over (partition by foo.category order by foo.category) as seqnum
      from foo join
           t
           on foo.category = t.category
     ) t
where seqnum <= t.cnt;

Upvotes: 1

Related Questions