mysql not reverse sorting datetime field correctly [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 10 years ago.

I am trying to sort a datetime field in descending order. It is sorting by day just fine. However, the time part is random. It sorts in ascending order perfectly.

Sample query:

SELECT * FROM pcvisit WHERE page_id='0005e1ca1784383bf6bf032f33dc6e27' ORDER BY dtime DESC

Result:

4adbc6b1cab4f14e7c9f2e308eb0944e | 0005e1ca1784383bf6bf032f33dc6e27 | 2011-02-23 16:08:35 | 1
733ab6507fbdab0e71f357f2f0ff6067 | 0005e1ca1784383bf6bf032f33dc6e27 | 2011-02-23 07:24:12 | 1
a5f9c9810e9648d2dbe4dec0e785216c | 0005e1ca1784383bf6bf032f33dc6e27 | 2011-02-23 05:26:59 | 1
981e24b4dd257f44a7a41dbdfe4def54 | 0005e1ca1784383bf6bf032f33dc6e27 | 2011-02-22 09:开发者_如何学C07:12 | 3
67906b350d59e97d7f56b7ceb254857e | 0005e1ca1784383bf6bf032f33dc6e27 | 2011-02-22 06:55:44 | 1

I have also tried:

SELECT * FROM pcvisit WHERE page_id='0005e1ca1784383bf6bf032f33dc6e27' ORDER BY dtime DESC, TIME(dtime) DESC

Trying my best to not have to split it into 2 fields.

---

The order in your second select is primarily on dtime and the time only affects it when the primary field is equal. In other words,

order by col1, col2

will effectively only use col2 to order those rows that have identical col1 values.

That's the way multi-column ordering works.

If you wanted something like ascending time within descending date, that would be a bit trickier but you'd just have to break out the date as well as time, something like:

order by date(dtime) desc, time(dtime) asc

And in that case, I really would consider breaking it into two separate columns for efficiency. You don't want to be doing per-row functions in your order by clauses - better to have separate indexes for them.

However, I don't think that's the case here. Since dtime is a date/time column, you're already sorting on both date and time so I'm not sure what you're trying to achieve beyond that. Just use order by dtime desc as in your first example and it sorts correctly.

---

The date and time are being sorted correctly. Look closer. When the day changes to the 22nd, the time starts back at the top again, and starts descending. Its descending the date first, then by the time.