I have designer and design table
designer has many designs
I want to get all designers with 10 designs for each designer
Is there way I can do it in a single query in either postgresql, mysql
For best performance in Postgres (not possible in MySQL) use a
SELECT d.designer, d1.design -- whatever columns you need
FROM designer d
LEFT JOIN LATERAL (
SELECT * -- or just needed columns
WHERE designer_id = d.designer_id
-- ORDER BY ??? -- you may want to pick certain designs
) d1 ON true
ORDER BY d.designer_id, d.design_id; -- or whatever
This gives you 10 designs per designer - or as many as he/she has if there are fewer.
LEFT JOIN LATERAL ... ON truekeeps designers in the result that don't have a single design (yet).
- What is the difference between LATERAL and a subquery in PostgreSQL?
- Call a set-returning function with an array argument multiple times
You get best performance if you add an
ORDER BYclause that matches an existing index on
CREATE INDEX foo ON design (designer_id, design_id)
Then, in the subquery
d1in above query:
ORDER BY design_id
Now Postgres can pick the top items from the index directly.
Related answer with more details:
- Limit number of rows per group from join (NOT to 1 row)
select * from (
Select row_number() OVER (PARTITION BY a.designer ORDER BY b.design DESC) rn,
inner join b
on a.id = b.id
where rn <= 10
mysql doesn't have window functions, which you need, so this is postgresql.
In MySQL, you can make use of user-defined variables to emulate some of the analytic functions available in other databases...
FROM ( SELECT @rn := IF(r.id = @prev_r_id,@rn+1,1) AS rn
, @prev_r_id := r.id AS designer_id
, d.id AS design_id
FROM (SELECT @rn := 0, @prev_r_id := NULL) i
JOIN designer r
JOIN design d
ON d.designer_id = r.id
ORDER BY r.id, d.id
WHERE v.rn <= 10
ORDER BY v.designer_id, v.design_id
You can run just the inline view query (
v) to see what that returns. What it does is order the rows by
designer_id, and compares the value from the current row to the value from the previous row... if they match, it increments
@rn by 1, otherwise, it resets
@rn to 1. Net effect is that we get an ascending integer sequence from
rn for each designer_id... 1,2,3.
The outer query filters out the rows where
rn is greater than 10.
If a given
designer has fewer than ten
designs, we get fewer than ten rows for that