-3

I have a table "test":

ID | NAME | DATETIME
---+------+-----------------------
1  | a1   | 2023-05-01 01:00:00
2  | a2   | 2023-05-01 02:00:00
3  | a3   | 2023-05-01 03:00:00
4  | b1   | 2023-05-02 01:00:00
5  | b2   | 2023-05-02 02:00:00
6  | b3   | 2023-05-02 03:00:00

If I do a simple DESCend query like that:

SELECT * FROM `test` ORDER by `DATETIME` DESC

I don't recieve exactly what I need. It returns 6, 5, 4, 3, 2, 1 ID's. But how can I group my result by DATE and get a result sorted like that:

ID's in the desired result: 4, 5, 6, 1, 2, 3

As you can see, I need to sort each day by time ASC, but at the same time, starting from the last DATETIME.

I have no idea where to start and how to do such a request. Please help.

I tried to make temp arrays for each day by using PHP and resort them, but this is a bad decision, cause I also have a LIMIT 0, 20 in my query for page navigator, so it's bad at the end, cause it might be not a full day...

Qirel
  • 25,449
  • 7
  • 45
  • 62
Jim Hopper
  • 13
  • 1
  • 4
  • Also, https://stackoverflow.com/questions/366603/group-by-date-only-on-a-datetime-column – OMi Shah May 15 '23 at 09:44
  • also, https://stackoverflow.com/questions/366603/group-by-date-only-on-a-datetime-column#:~:text=Cast%20the%20datetime%20to%20a,mydate)%3B – OMi Shah May 15 '23 at 09:44

1 Answers1

1

You need two terms in the order by clause - first descending by the date extracted from the datetime, and then ascending by the time extracted from it:

SELECT   * 
FROM     `test` 
ORDER BY DATE(`datetime`) DESC, TIME(`datetime`) ASC
Stu
  • 30,392
  • 6
  • 14
  • 33
Mureinik
  • 297,002
  • 52
  • 306
  • 350