Order result form Mysql group by

Question

Ok i got this table

+-----+-----------------------------+-------------+-------------+----------+---------+
| id  | title                       | created     | updated     | category | content |
+-----+-----------------------------+---------------------+---------------------+----+
| 423 | What If I Get Sick and Die? | 2008-12-30  | 2009-03-11  | angst    | NULL    |
| 524 | Uncle Karl and the Gasoline | 2009-02-28  | NULL        | humor    | NULL    |
| 537 | Be Nice to Everybody        | 2009-03-02  | NULL        | advice   | NULL    |
| 573 | Hello Statue                | 2009-03-17  | NULL        | humor    | NULL    |
| 598 | The Size of Our Galaxy      | 2009-04-03  | NULL        | science  | NULL    |
+-----+-----------------------------+---------------------+---------------------+----+

I try to get unique row for each category, so i query fo something like:

SELECT id,title,category FROM `entries` group by category

And the result is :

+-----+-----------------------------+----------+
| id  | title                       | category |
+-----+-----------------------------+----------+
| 537 | Be Nice to Everybody        | advice   |
| 423 | What If I Get Sick and Die? | angst    |
| 524 | Uncle Karl and the Gasoline | humor    |
| 598 | The Size of Our Galaxy      | science  |
+-----+-----------------------------+----------+

The result looks fine, but how can i get row with category = humor and id = 573 instead?

If i query something like:

SELECT id,title,category, max(id) FROM `entries` group by category

I got result like this:

+-----+-----------------------------+----------+---------+
| id  | title                       | category | max(id) |
+-----+-----------------------------+----------+---------+
| 537 | Be Nice to Everybody        | advice   |     537 |
| 423 | What If I Get Sick and Die? | angst    |     423 |
| 524 | Uncle Karl and the Gasoline | humor    |     573 |
| 598 | The Size of Our Galaxy      | science  |     598 |
+-----+-----------------------------+----------+---------+

Obviously thats not what i want, any help would be appreciated

And i want to know how exactly mysql collapse row for each group? Did mysql simply take first row for each group?

Yes, it simply takes first row. But you can't take for granted which row it will take as first, next time you run the same query. (first as "inserted first", "first in index order", etc...) — ypercubeᵀᴹ, Apr 03 '11 at 20:15
You can actullay sometimes use this (only to MySQL) "feature" to get a "random" row from a table. Problem is, it's not very random... — ypercubeᵀᴹ, Apr 04 '11 at 11:53

ypercubeᵀᴹ · Accepted Answer · 2011-04-03T20:09:33.927

Using JOIN

and a query with GROUP BY to get maximum id for every category (I guess you do want the row with maximum id for every category, don't you?)

SELECT e.id
     , e.title
     , e.created
     , e.updated
     , e.category
     , e.content
FROM entries e
JOIN
    ( SELECT max(id) AS maxid
      FROM entries
      GROUP BY category
    ) AS cat
ON e.id = cat.maxid

Using IN

and a query to get maximum id for every category

SELECT id
     , title
     , created
     , updated
     , category
     , content
FROM entries
WHERE id IN
    ( SELECT max(id)
      FROM entries
      GROUP BY category
    )

Using ANY

and a correlated subquery

SELECT e.id
     , e.title
     , e.created
     , e.updated
     , e.category
     , e.content
FROM entries e
WHERE e.id >= ANY
    ( SELECT cat.id
      FROM entries cat
      WHERE e.category = cat.category
    )

Using NOT EXISTS

and a correlated subquery

SELECT e.id
     , e.title
     , e.created
     , e.updated
     , e.category
     , e.content
FROM entries e
WHERE NOT EXISTS
    ( SELECT 1
      FROM entries cat
      WHERE cat.id > e.id
        AND e.category = cat.category
    )

score -1 · Answer 2 · answered Apr 03 '11 at 17:34

-1

I think this is what you want. I haven't tested it though.

select * from entries e
inner join (select max(id), category from entries group by category) ids
on e.id=ids.id

answered Apr 03 '11 at 17:34

prauchfuss

1,930
3
17
20

Not totally, if he changes `select max(id)` to `select max(id) as maxid` and `on e.id=ids.id` to `on e.id=ids.maxid`, it's (almost) the same as my first one. – ypercubeᵀᴹ Apr 04 '11 at 11:51

Order result form Mysql group by

2 Answers2

Linked