GROUP BY query ignores ORDER BY clause

开发者_开发知识库SELECT
    deal_woot.*,
    site.woot_off,
    site.name AS site_name
FROM deal_woot
INNER JOIN site ON site.id = site_id
WHERE site_id IN (2, 3, 4, 5, 6)
GROUP BY site_id
ORDER BY deal_woot.id DESC
LIMIT 5

I'd like to ORDER BY before grouping, how can I accomplish this?

With a sub-query like: SELECT *,COUNT(*) FROM (SELECT * from actions order by date DESC) AS actions GROUP BY ip;

Since you are grouping by site_id, only 1 deal_woot row will be returned. Try ordering by MAX() which will return the highest id for every site_id.

SELECT
    deal_woot.*,
    site.woot_off,
    site.name AS site_name
FROM deal_woot
INNER JOIN site ON site.id = site_id
WHERE site_id IN (2, 3, 4, 5, 6)
GROUP BY site_id
ORDER BY MAX(deal_woot.id) DESC
LIMIT 5

Note: Since it is UB what deal_woot row will actually be returned, try spiting up your query:

SELECT
    deal_woot.*,
    site.woot_off,
    site.name AS site_name
FROM site JOIN (
    SELECT site_id, MAX(deal_woot.id) MaxID
    FROM deal_woot
    WHERE site_id IN (2, 3, 4, 5, 6)
    GROUP BY site_id
  ) sg ON site.id = sg.site_id
  JOIN deal_woot
    ON site.id = deal_woot.site_id AND deal_woot.id = sg.MaxID
ORDER BY sg.MaxID DESC
LIMIT 5

You are performing GROUP BY on site_id and you are arranging your records on deal_woot.id.

Even you do Order By prior to Group the output will remain same.

Do you have any specific requirement to do the things as the doubt you are having have nothing to do with the Order by and group by.

SELECT
     deal_woot.*,
     site.woot_off,
     site.name AS site_name 
FROM deal_woot 
INNER JOIN site ON site.id = site_id 
WHERE site_id IN (2, 3, 4, 5, 6) 
GROUP BY deal_woot.id DESC, site_id 
LIMIT 5

I assume that deal_woot.id is unique that Grouping will be based on site_id