'limitation of each order in Postgresql

I have 3 tables news, category, and category_request I try to fetch all the categories from the news by this query

SELECT *
FROM category_request t1 inner join news t2 on t1.news_id  = t2.news_id inner join category t3 on t1.category_id = t3.category_id 
order by t3.title ;

and the result is here

I would love to know if is there any way to get a limit for each order?.

for example just 3 Entertainment news and 3 Feature news Thanks in advance.

postgresql


Solution 1:[1]

Windowing functions will do the job :

WITH
T AS 
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY title ORDER BY NULL) AS N
FROM   category_request t1 
       inner join news t2 on t1.news_id  = t2.news_id 
       inner join category t3 on t1.category_id = t3.category_id 
)
SELECT *
FROM   T
WHERE  N <= 3
order by title ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SQLpro

Related Questions