CODEWITHSUNDEEP
phpmysql
Tatata
Tatata

Reputation: 31

SQL GROUP BY and ORDER BY not working correctly

I have query:

SELECT p.* FROM `our_projects` AS p 
LEFT OUTER JOIN `news_categories` nc ON p.sort = nc.categories_id 
LEFT OUTER JOIN `news` n ON nc.news_id = n.id
GROUP BY p.id
ORDER BY n.date_show DESC

our_projects table: have 9 records
news_categories table: have multiple records, this is categories of news.
news: this is table of news.

I want order by date_show column of news, but group by not working correctly. I get not correctly order, not by date_show of news.

I get in table our_project ORDER BY ID DESC WHY??:

1) 108 - last news 25 feb
2) 111 - last news 24 feb
3) 114 - last news 24 feb
4) 117 - last news 9 feb
5) 120 - last news 23 feb
6) 123 - last news 24 feb
7) 126 - last news 23 feb
8) 129 - last news 9 feb
9) 132 - last news 22 feb

Why??

Upvotes: 0

Views: 146

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522074

One fix for what you want would be to join to a subquery which identifies the latest news story for each new category. In the query below, the subquery aliased as t does precisely this. It will filter off all news stories except for the most recent one per category.

SELECT p.*, nc.*, n.*
FROM our_projects p 
LEFT JOIN news_categories nc
    ON p.sort = nc.categories_id 
LEFT JOIN news n
    ON nc.news_id = n.id
INNER JOIN
(
    SELECT t1.categories_id, MAX(t2.date_show) AS max_date_show
    FROM news_categories t1
    INNER JOIN news t2
        ON t1.news_id = t2.id
    GROUP BY t1.categories_id
) t
    ON nc.categories_id = t.categories_id AND
       n.date_show      = t.max_date_show
GROUP BY
    p.id
ORDER BY
    n.date_show;

Upvotes: 1

Related Questions