Order and Group rows in Mysql query

Question

Iam trying to execute the following mysql query and getting mentioned error.

SELECT * FROM `selector` WHERE `to_id`='44' ORDER BY `time` DESC 
GROUP BY `event_id`, `entity_guid`;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY event_id, entity_guid LIMIT 0, 25' at line 1

Can anybody help to correct/rewrite this query as I need to order the rows in descending order first time and then eliminate the duplicate entries by grouping event_id and entity_guid

Below is the representation of my SELECTOR table

|------|-------|----------|-------------|-------------|
|fr_id | to_id | event_id | entity_guid | time        |
|------|-------|----------|-------------|-------------|
| 44   | 54    |  4       | 0           | 1531121058  |
|------|-------|----------|-------------|-------------|
| 54   | 44    |  3       | 0           | 1531063718  |
|------|-------|----------|-------------|-------------|
| 54   | 44    |  2       | 609         | 1531063431  |
|------|-------|----------|-------------|-------------|
| 54   | 44    |  1       | 608         | 1530975443  |
|------|-------|----------|-------------|-------------| 
| 150  | 44    |  1       | 608         | 1531542247  |
|------|-------|----------|-------------|-------------|

The actual result I needed is as follows

|------|-------|----------|-------------|-------------| 
| 150  | 44    |  1       | 608         | 1531542247  |
|------|-------|----------|-------------|-------------|
| 54   | 44    |  3       | 0           | 1531063718  |
|------|-------|----------|-------------|-------------|
| 54   | 44    |  2       | 609         | 1531063431  |
|------|-------|----------|-------------|-------------|

if i remember correctly should be first group and then order... — Lelio Faieta, Jul 20 '18 at 15:46
Welcome to Stack Overflow. You are misusing MySQL's non-standard extension to `GROUP BY`. Please read [this](https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html). Your query is likely to yield unpredictable results. — O. Jones, Jul 20 '18 at 15:56
Well I suspect max() will return only one result not the expected result of ORDER BY DESC (more than 1 results) based on to_id — Thomas, Jul 20 '18 at 16:01
I have updated the how actual results needs to be, please check — Thomas, Jul 20 '18 at 16:17
Actually the pointed one was not infinite, written query as my own way and achieved the accurate result, Thanks for all your help — Thomas, Jul 21 '18 at 12:59

score 0 · Answer 1 · answered Jul 20 '18 at 15:46

0

The ORDER BY clause goes after the GROUP BY clause.

answered Jul 20 '18 at 15:46

O. Jones

103,626
17
118
172

already tried that, but it is not providing expected result, first I need to order them in descending by using time (timestamp) then group them – Thomas Jul 20 '18 at 15:49
SELECT * FROM(SELECT * FROM `selectors` WHERE `to_id`='44' ORDER BY `time` DESC) as d GROUP BY d.event_id, d.entity_guid; even the above query failed – Thomas Jul 20 '18 at 15:53
@Thomas Ordering doesn't affect how grouping is done, why do you think you need to do it first? – Barmar Jul 20 '18 at 15:59
yep, I need to display the results based on timestamp using ORDER BY DESC, so the latest added rows will displayed first, then eliminate the duplicated rows, look at my table format, so the actual result needed is eliminate the second last row by displaying the last row eg.fr_id =150 (based on time stamp) – Thomas Jul 20 '18 at 16:11
For my question the result can be achieved using following query, for the information of attendees in this thread `SELECT * FROM TABLE_NAME WHERE (to_id,time) IN(SELECT to_id, MAX(time) FROM TABLE_NAME WHERE to_id='id' GROUP BY `entity_guid`, `event_id`) ORDER BY time DESC LIMIT 0,3 ` – Thomas Jul 21 '18 at 15:29
@Thomas when you answer your own question on StackOverflow, it's customary to write an Answer. Why? StackOverflow is, at its heart, an archive of questions and answers. Comments don't get indexed as efficiently. – O. Jones Jul 22 '18 at 10:47
@Jon Thanks, yes I know but I am unable to answer my question :( – Thomas Jul 22 '18 at 13:48

score 0 · Answer 2 · answered Jul 20 '18 at 15:49

0

Your Query should be like this

SELECT `event_id`, `entity_guid` FROM `selector` 
WHERE `to_id`='44'
    GROUP BY `event_id`, `entity_guid`
     ORDER BY `time` DESC

If you need more help then please share your data set and expected output

answered Jul 20 '18 at 15:49

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks, but why not * ,not only event_id entity_guid? not expecting that – Thomas Jul 20 '18 at 15:55
there is some rules for using group by think you need to know those rules 1st – Zaynul Abadin Tuhin Jul 20 '18 at 15:58

Order and Group rows in Mysql query

2 Answers2