ORDER BY before GROUP BY
I have the tb_user
| id | name | info |
| 01 | Peter | text1 |
| 02 | Paul | text1 |
| 03 | Peter | text2 |
| 04 | Peter | text3 |
| 05 | Paul | text2 |
... and I need the last row of GROUP BY.
The easy SQL is:
SELECT COUNT(name), name, info FROM tb_user GROUP BY name ORDER BY id;
Tab1:
| COUNT(name) | name | info |
| 3 | Peter | text1 |
| 2 | Paul | text1 |
But I need the LAST info!!!
Tab2:
| COUNT(name) | name | info |
| 3 | Peter | text3 |
| 2 | Paul | text2 |
I use this SQL:
SELECT COUNT(name), name, info FROM (SELECT * FROM tb_user ORDER BY id DESC) as tb_temp GROUP BY name ;
But that's don't work!
| COUNT(name) | name | info |
| 3 | Peter | text1 |
| 2 | Paul | text1 |
All the same:
SELECT COUNT(name), name, info FROM (SELECT * FROM tb_user ORDER BY id DESC) as tb_temp GROUP BY name ORDER BY tb_temp.id DESC; SELECT COUNT(name), name, info FROM (SELECT * FROM tb_user ORDER BY id DESC) as tb_temp GROUP BY name ORDER BY tb_temp.id ASC;
Who can help me? What can I do?
Answer Answered by Ian Gilfillan in this comment.
As the GROUP BY article states, "If you select a non-grouped column or a value computed from a non-grouped
column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY SQL_MODE is used." So you can't guarantee the order. You however use a function such as MAX(info) to get a specific value.
SELECT COUNT(name), name, MAX(info) FROM tb_user GROUP BY name ORDER BY id; +-------------+-------+-----------+ | COUNT(name) | name | MAX(info) | +-------------+-------+-----------+ | 3 | Peter | text3 | | 2 | Paul | text2 | +-------------+-------+-----------+
Since the text is unlikely to be sortable in that way, here's an extremely horrible hack that would work, assuming your ids increment:
SELECT COUNT(name), name, MAX(CONCAT(id,': ',info)) FROM tb_user GROUP BY name ORDER BY id; +-------------+-------+---------------------------+ | COUNT(name) | name | MAX(CONCAT(id,': ',info)) | +-------------+-------+---------------------------+ | 3 | Peter | 4: text3 | | 2 | Paul | 5: text2 | +-------------+-------+---------------------------+
Of course, you probably want to remove the id from the info field, so taking the hack further, and assuming the id length never exceeds 50:
SELECT COUNT(name), name, SUBSTR(MAX(CONCAT(LPAD(id,50),info)),51) AS info FROM tb_user GROUP BY name ORDER BY id; +-------------+-------+-------+ | COUNT(name) | name | info | +-------------+-------+-------+ | 3 | Peter | text3 | | 2 | Paul | text2 | +-------------+-------+-------+
