Geek Answers Handbook

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 want ORDER BY before grouping, how can I do this?

+5
source share
4 answers
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 deal_woot.id is unique in that the grouping will be based on site_id

-1
source

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

+1
source

site_id, 1 deal_woot. MAX(), id 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

. UB, deal_woot , :

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
0

GROUP BY site_id, deal_woot.id.

Even if you order before the group, the output will remain the same.

Do you have any specific requirement to do what you doubt has nothing to do with order and group.

0
source

More articles:


All Articles