Select The last 3 news from each category - Two tables - (MySQL - PHP)

Question

I want to select the last 3 news from each category, from two table

first table ' Categories ' & ' news '

categories table

ID_CAT | NAME | PRIORITE

News table

ID | ID_CAT | TITLE | THE_NEWS

I try to do limit in this SQL code, but it gives me just the last 3 news from all categories

SELECT C.PRIORITE, N.* 
  FROM categories C, news N 
 WHERE N.ID_CAT=C.ID_CAT 
   AND C.PRIORITE >1 
ORDER BY N.ID DESC 
LIMIT 3

I try to get all news that have PRIORITE > 1 ( The priorite is the order of category ) So, I want to get 3 last news from each priorite.

Example :

Priorite 2 = get last 3 news
Priorite 3 = get last 3 news 

... etc

I made some search on the internet, but nothing worked for me, is there any solution? Or do I need to create another function to get news from each category with ID sending in parameters of it?

Answer 1

You could get n no. of news for each category by using a correlated sub query

SELECT *
FROM news n
JOIN categories c ON c.id = n.category_id
WHERE c.priority > 1
AND (
  SELECT COUNT(*)
  FROM news 
  WHERE n.category_id = category_id
  AND n.id <= id
) <= 3; 

Demo

Or if you are using Mysql 8 then you could make use of window functions with common table expressions like

WITH cte AS(
    SELECT *, 
    RANK() OVER (PARTITION BY category_id ORDER BY id DESC ) rnk
    FROM news
    ORDER BY id

)

SELECT *
FROM cte n
JOIN categories c ON c.id = n.category_id
WHERE c.priority > 1
AND rnk <= 3;

Demo

Answer 2

i suggest divide it 2 parts, instead of complex query,

in 1st part you can get categories

select * from Categories

then loop categories and in loop get their 3 news,

select * from news where cat_id = '' order by desc id limit 3

if you try both and check the execution time, you see loop take less time. because news are rapidly growing stuff. i know you think why i call query again and again but my teacher said prefer multiple queries instead of complex one