CODEWITHSUNDEEP
mysqlsql
charlie
charlie

Reputation: 481

SQL GROUP BY does not keep correct order by

I have a table that I am running this query on:

SELECT datetime, from_email, from_name
FROM emails
WHERE user_seq = '1'
GROUP BY from_email
ORDER BY datetime DESC

the query executes and returns data however it is not showing the data in the order of datetime DESC

When I remove the GROUP BY, the data shows in the correct order.

Upvotes: 1

Views: 79

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

I think ,you want to group the results using from_email.. Try with the below script.. and the script is for SQL server..

;WITH cte_1
as( SELECT datetime, from_email, from_name
           , ROW_NUMBER ()OVER(PARTITION BY from_email ORDER BY datetime desc) RNo
     FROM emails
     WHERE user_seq = '1')
SELECT datetime, from_email, from_name
FROM cte_1
WHERE RNO=1

Following code seems to work in MySql..

SELECT datetime, from_email, from_name
FROM emails e
 JOIN (SELECT from_email,MAX(datetime) MaxDate
       FROM emails
       GROUP BY from_email)t on e.from_email=t.from_email AND e.datetime=t.MaxDate

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

Looks like you are using mysql as it allows you to exclude fields from the group by clause that aren't included in an aggregate, in this case, your datetime field. Considering you want to sort the datetime descending, perhaps you really just need to use max:

SELECT max(datetime), 
       from_email, 
       from_name 
FROM emails 
WHERE user_seq = '1' 
GROUP BY from_email, from_name
ORDER BY 1 desc

Upvotes: 4

Related Questions