ORDER BY date and time BEFORE GROUP BY name in mysql

Question

i have a table like this:

name    date         time
tom | 2011-07-04 | 01:09:52
tom | 2011-07-04 | 01:09:52
mad | 2011-07-04 | 02:10:53
mad | 2009-06-03 | 00:01:01

i want oldest name first:

SELECT * 
ORDER BY date ASC, time ASC 
GROUP BY name

(->doesn't work!)

now it should give me first mad(has earlier date) then tom

but with GROUP BY name ORDER BY date ASC, time ASC gives me the newer mad first because it groups before it sorts!

again: the problem is that i can't sort by date and time before i group because GROUP BY must be before ORDER BY!

group by is used with aggregate functions, not for sorting - are you sure you've got this right? — Neville Kuyt, Jul 04 '11 at 13:27
do you have a better idea? i thought about distinct but then also date and time is distinct. — CodingYourLife, Jul 04 '11 at 13:34
If there was some way to combine the date and time columns, is what you actually want just the name and *the earliest* datetime value for each name? — Damien_The_Unbeliever, Jul 04 '11 at 13:36
result should be: mad | 2009-06-03 | 00:01:01 /\ tom | 2011-07-04 | 01:09:52 — CodingYourLife, Jul 04 '11 at 13:39
your select doesn't have an aggregate function, so "group by" doesn't do anything. Scorpio's answer should work... — Neville Kuyt, Jul 04 '11 at 14:05

score 99 · Accepted Answer · edited Feb 12 '20 at 03:08

99

Another method:

SELECT * 
FROM (
    SELECT * FROM table_name
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected.

I prefer this method as the subquery makes logical sense rather than peppering it with other conditions.

edited Feb 12 '20 at 03:08

omor faruk

81
1
7

answered Dec 17 '12 at 00:20

swbeeton

1,146
1
8
4

29

Note that this approach will unfortunately not work on MariaDB (although MariaDB is considered a drop-in replacement for MySQL): https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/ – rinogo Oct 12 '15 at 23:30
6

There seems to be a FROM missing in the subquery. – John Reid May 27 '16 at 15:01
2

This is getting really slow with large data sets if the sub query returns a big set. – Nimeshka Srimal Oct 04 '16 at 11:00
8

Its not woking with my db. i am using mysql 5.7.17 – Sudhanshu Jain Mar 01 '17 at 12:11
20

Adding "LIMIT 18446744073709551615" in subquery should fix things on MariaDB, however it's a dirty solution :) – gnysek Apr 11 '18 at 12:23
@JohnReid Its missing in question also :p – Ahsaan Yousuf Jun 08 '18 at 06:53
@ringo What is the above query's equivalent for Mariadb ? – NixMan Feb 05 '21 at 06:50

score 63 · Answer 2 · answered Feb 04 '16 at 13:48

63

As I am not allowed to comment on user1908688's answer, here a hint for MariaDB users:

SELECT *
FROM (
     SELECT *
     ORDER BY date ASC, time ASC
     LIMIT 18446744073709551615
     ) AS sub
GROUP BY sub.name

https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

answered Feb 04 '16 at 13:48

Vincent

857
7
6

This is exactly the solution I was looking for. Thank you for the source ! – naurel Nov 22 '17 at 13:14
3

I cringe at how hacky this is, but it does seem to be the only way to get this to work on MariaDB... – rinogo May 01 '18 at 19:53
after exploring about two hours, It is the solution that worked. – kamran May 03 '18 at 05:07
This worked. Really don't like the looks of it, but what can you do... – Christopher Smit Apr 23 '20 at 13:53
This is the only solution that works properly. Thank you @Vincent – Milan Katariya Jul 16 '20 at 13:47
Thanks! This work perfect, i'm going crazy with this problem! – gokuhs Mar 27 '22 at 08:40
I am using MySQL 8 and it requires the `LIMIT` to give the desired result. – Kohjah Breese May 24 '22 at 10:49

Cyril Gandon · Answer 3 · 2011-07-04T14:32:13.243

30

I think this is what you are seeking :

SELECT name, min(date)
FROM myTable
GROUP BY name
ORDER BY min(date)

For the time, you have to make a mysql date via STR_TO_DATE :

STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s')

So :

SELECT name, min(STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s'))
FROM myTable
GROUP BY name
ORDER BY min(STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s'))

edited Jul 04 '11 at 14:32

answered Jul 04 '11 at 13:39

Cyril Gandon

16,830
14
78
122

i don't know if this is really the answer to my problem. it doesn't seem to sort correctly with the time. could you please check if the syntax here is correct: ($sql='SELECT * FROM table GROUP BY name';) $sql.=" ORDER BY min(STR_TO_DATE(orderDate + ' ' + orderTime, '%m/%d/%Y %h:%i:%s'))"; – CodingYourLife Jul 04 '11 at 13:54
You can't perform a 'SELECT *' if you are grouping. Change the SELECT part of your query. – Cyril Gandon Jul 04 '11 at 14:02
ok and how can i now say $row["date"]? it doesn't sort correctly... it just says NULL for date and the order not sorted. – CodingYourLife Jul 04 '11 at 14:28
I made a mistake, it is not %m/%d/%Y that you need but %Y-%m-%d. I updated the query. – Cyril Gandon Jul 04 '11 at 14:32
yeah and i think in my case it's %H because i have 13:05(you couldn't have known this) but i still get NULL for date and time and no sorting. PS: your first method works but i really need date and time. – CodingYourLife Jul 04 '11 at 14:42
ok i have it...: $sql='SELECT name, date, time min(date), min(STR_TO_DATE(time, "%H:%i:%s")) FROM '.$table; – CodingYourLife Jul 04 '11 at 14:48
11

I don't think that this works. There's no assurance that the name column that gets returned is from the same row as the date. If this appears to have solved your problem, be aware that its more than likely unstable. – Rob Forrest Aug 08 '12 at 15:03
This doesn't do what the OP wanted. Rob is correct, the results returned are not from the same rows. – Gavin Aug 21 '13 at 12:22
Sorry for the downvote, but this answer simply isn't correct. The answer user1908688 provided is a much better solution. – GordonM Jan 28 '15 at 11:56
This needs to be deselected as the accepted answer. `GROUP BY` is processed before `ORDER BY` which is the whole reason a SO question exists like this in the first place! Like the "other" solution proposes, although a subquery is not efficient, it is a straightforward and correct approach to making this work. – rinogo Oct 12 '15 at 22:51
Sorry, downvoted because this is merely answering the OP's question because he has only two rows, so apparently the returned result looks okay. But if you have more columns, you see that the returned results are not from the same rows. – Nimeshka Srimal Aug 25 '16 at 11:29
Sorry for deselecting this as the accepted answer. Meanwhile enough people indicated user1908688 to be the the more correct one and if this answer works some but not all the time I think it's not fair to show it on top as accepted answer – CodingYourLife Feb 10 '17 at 21:00

score 21 · Answer 4 · edited Feb 10 '17 at 20:27

21

This worked for me:

SELECT *
FROM your_table
WHERE id IN (
    SELECT MAX(id)
    FROM your_table
    GROUP BY name
);

edited Feb 10 '17 at 20:27

FelixSFD

6,052
10
43
117

answered Feb 10 '17 at 16:44

jokermt235

478
6
9

score 4 · Answer 5 · answered Jul 04 '11 at 13:34

4

Use a subselect:

select name, date, time
from mytable main
where date + time = (select min(date + time) from mytable where name = main.mytable)
order by date + time;

answered Jul 04 '11 at 13:34

Bohemian

412,405
93
575
722

score 2 · Answer 6 · answered Dec 07 '21 at 08:47

2

If you wont sort by max date and group by name, you can do this query:

SELECT name,MAX(date) FROM table  group by name ORDER BY name

where date may by some date or date time string. It`s response to you max value of date by each one name

answered Dec 07 '21 at 08:47

Alexandr Tutkevich

272
2
3
10

score 1 · Answer 7 · answered Nov 01 '18 at 22:56

Another way to solve this would be with a LEFT JOIN, which could be more efficient. I'll first start with an example that considers only the date field, as probably it is more common to store date + time in one datetime column, and I also want to keep the query simple so it's easier to understand.

So, with this particular example, if you want to show the oldest record based on the date column, and assuming that your table name is called people you can use the following query:

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date > p2.date
WHERE p2.date is NULL
GROUP BY p.name

What the LEFT JOIN does, is when the p.date column is at its minimum value, there will be no p2.date with a smaller value on the left join and therefore the corresponding p2.date will be NULL. So, by adding WHERE p2.date is NULL, we make sure to show only the records with the oldest date.

And similarly, if you want to show the newest record instead, you can just change the comparison operator in the LEFT JOIN:

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date < p2.date
WHERE p2.date is NULL
GROUP BY p.name

Now, for this particular example where date+time are separate columns, you would need to add them in some way if you want to query based on the datetime of two columns combined, for example:

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date + INTERVAL TIME_TO_SEC(p.time) SECOND > p2.date + INTERVAL TIME_TO_SEC(p2.time) SECOND
WHERE p2.date is NULL
GROUP BY p.name

You can read more about this (and also see some other ways to accomplish this) on the The Rows Holding the Group-wise Maximum of a Certain Column page.

Very beautiful and mindblowing solution but also very slow. Just checked it on table with 1.5M rows, my mysql server has gone away. Solution with subquery and limit (for fix mariadb bug) is much faster. — c0deMaster, Oct 29 '22 at 13:23

score 0 · Answer 8 · answered Jul 21 '17 at 20:06

I had a different variation on this question where I only had a single DATETIME field and needed a limit after a group by or distinct after sorting descending based on the datetime field, but this is what helped me:

select distinct (column) from
(select column from database.table
order by date_column DESC) as hist limit 10

In this instance with the split fields, if you can sort on a concat, then you might be able to get away with something like:

select name,date,time from
(select name from table order by concat(date,' ',time) ASC)
as sorted

Then if you wanted to limit you would simply add your limit statement to the end:

select name,date,time from
(select name from table order by concat(date,' ',time) ASC)
as sorted limit 10

score 0 · Answer 9 · answered Sep 27 '19 at 03:16

0

In Oracle, This work for me

SELECT name, min(date), min(time)
    FROM table_name
GROUP BY name

answered Sep 27 '19 at 03:16

Max

1
1

score 0 · Answer 10 · answered Mar 26 '20 at 11:29

0

work for me mysql select * from (SELECT number,max(date_added) as datea FROM sms_chat group by number) as sup order by datea desc

answered Mar 26 '20 at 11:29

SirajuddinLuck

141
1
8

score -1 · Answer 11 · answered May 17 '18 at 07:55

This is not the exact answer, but this might be helpful for the people looking to solve some problem with the approach of ordering row before group by in mysql.

I came to this thread, when I wanted to find the latest row(which is order by date desc but get the only one result for a particular column type, which is group by column name).

One other approach to solve such problem is to make use of aggregation.

So, we can let the query run as usual, which sorted asc and introduce new field as max(doc) as latest_doc, which will give the latest date, with grouped by the same column.

Suppose, you want to find the data of a particular column now and max aggregation cannot be done. In general, to finding the data of a particular column, you can make use of GROUP_CONCAT aggregator, with some unique separator which can't be present in that column, like GROUP_CONCAT(string SEPARATOR ' ') as new_column, and while you're accessing it, you can split/explode the new_column field.

Again, this might not sound to everyone. I did it, and liked it as well because I had written few functions and I couldn't run subqueries. I am working on codeigniter framework for php.

Not sure of the complexity as well, may be someone can put some light on that.

Regards :)

I guess, I have escaped sub query here, and things could get work in just one query with minute changes. Correct me please if I happen to miss something here, Thanks again! — Satys, May 17 '18 at 07:57

ORDER BY date and time BEFORE GROUP BY name in mysql

11 Answers11

Linked