CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
holden321
holden321

Reputation: 13

Limit per some field

Suppose we have such an sql query, with joined data from another table.

SELECT 
    pr.num, pr.resort_id, p.src_mask
FROM 
    rutraveler.rt_photo_resort AS pr
    JOIN rutraveler.rt_photo AS p ON pr.photo_id = p.id
WHERE   pr.resort_id = '612' AND p.src_mask is not null
ORDER BY num
LIMIT 30

So far we have to do several queries for several resort_id.
How to change the query so that we have only one query (WHERE resort_id in (612, 333, 111) with result no more than 30 items per each resort_id?

Upvotes: 1

Views: 60

Answers (2)

Ashish Rajput
Ashish Rajput

Reputation: 1529

you can use CTE with ROW_NUMBER() and PARTITION BY 

WITH Results_CTE AS
(
    SELECT 
    pr.num, pr.resort_id, p.src_mask,ROW_NUMBER() over ( PARTITION BY pr.resort_id ORDER BY num) As Row_number
FROM 
    rutraveler.rt_photo_resort AS pr
    JOIN rutraveler.rt_photo AS p ON pr.photo_id = p.id
WHERE   pr.resort_id IN (A,B,C) AND p.src_mask is not null   
)

SELECT *
FROM Results_CTE
WHERE Row_number <= 30

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Use ROW_NUMBER to count the rows per resort_id.

SELECT resort_id, num, resort_id, src_mask 
FROM
(
  SELECT 
    pr.resort_id, pr.num, pr.resort_id, p.src_mask,
    ROW_NUMBER() OVER (PARTITION BY pr.resort_id ORDER BY num) AS rn
  FROM 
    rutraveler.rt_photo_resort AS pr
    JOIN rutraveler.rt_photo AS p ON pr.photo_id = p.id
  WHERE resort_id in (612, 333, 111) AND p.src_mask is not null
) data
WHERE rn <= 30
ORDER BY resort_id, num;

Upvotes: 2

Related Questions