RANK() over (PARTITION BY) To show only TOP 3 rows for each month-CodePudding

I have a question about ranking . (My using Pgadmin for my SQL codes) Mange to get my sum of sales in DESC order and rank 1 to 3 for the month of APR But how can I achieve my result by showing only rank 1 to 3 for the month of Apr , May and June. I need to reflect only 9 rows in my table .

SELECT restaurant_id,
       EXTRACT(year FROM submitted_on) AS year,
       EXTRACT(month FROM submitted_on) AS month,
       SUM(total_amount),
       RANK() OVER (PARTITION BY(extract(month from submitted_on)) 
                        ORDER BY SUM(total_amount) DESC) rank
  FROM orders
 WHERE submitted_on::date BETWEEN '2021-04-01' AND '2021-06-30'
 GROUP BY restaurant_id, year, month

CodePudding user response：

If you just want 3 records you should use row_number instead of rank. for your requirement you can do it in this way:

select t.* from (
    SELECT restaurant_id,
           EXTRACT(year FROM submitted_on) AS year,
           EXTRACT(month FROM submitted_on) AS month,
           SUM(total_amount),
           RANK() OVER (PARTITION BY(extract(month from submitted_on)) 
                        ORDER BY SUM(total_amount) DESC) rank
      FROM orders
     WHERE submitted_on::date BETWEEN '2021-04-01' AND '2021-06-30'
     GROUP BY restaurant_id, year, month
   ) t
  where rank <=3;