Sorting by date & time in descending order?

Sorting by desc in a nutshell

Sorting your DATETIME or TIMESTAMP field in descending (DESC) order gives all records from most recent to oldest.

Slicing data with subquery

If you need top 5 entries for a certain ID, a subquery saves the day:

SELECT * FROM (
  SELECT * FROM your_table
  WHERE id = 'chosen_id'
  ORDER BY your_datetime_column DESC
  LIMIT 5
) AS sorted_table
ORDER BY sorted_table.your_datetime_column DESC;


// Pro-tip: Always name your subqueries, future you will thank you 👩‍💻🤘.

Handling date part alone

In case you want to look at the date alone:

SELECT * FROM (
  SELECT *, DATE(your_datetime_column) AS date_only FROM your_table
  ORDER BY date_only DESC, your_datetime_column DESC
  LIMIT 5
) AS sorted_table
ORDER BY sorted_table.date_only DESC, sorted_table.your_datetime_column DESC;


// Yes, it's like inception with dates. Walking on the ceiling next? 😄.

Precise to the millisecond with UNIX_TIMESTAMP

For some extra scrutiny down to the millisecond, use UNIX_TIMESTAMP():

SELECT * FROM your_table 
ORDER BY UNIX_TIMESTAMP(your_datetime_column) DESC;


// Now that's being punctual! Every millsecond counts ⏱️.

When dates clash

Corner case, quite literally: when identical dates have different times:

SELECT * FROM your_table
ORDER BY DATE(your_datetime_column) DESC, TIME(your_datetime_column) DESC;


// When two dates walk into a SQL bar, one needs to come out first!

This way, we can ensure the right sequencing when dates are the same but times differ.

Run the trial

Give your query a go with a particular ID to affirm it's sorting right:

SELECT * FROM your_table
WHERE id = 'chosen_id'
ORDER BY your_datetime_column DESC;


// Like a backstage dress rehearsal before the grand SQL ballet! 🩰

Accuracy is key

Filter entries based on the particular ID to keep your results razor sharp:

SELECT * FROM your_table
WHERE id = 'chosen_id'
ORDER BY your_datetime_column DESC;


// Crystal clear results, just what you ordered!

Cross-verify your outcome to make sure sort precision is spot on.

Best practices

Here's your mini pocket guide for sorting:

• Aliases are your friends. Keeps code neat and unambiguous.
• Square off null values accordingly, don't let them skew your sort.
• Run your queries by diverse data sets for robustness.
• If a simpler query can get you there, skip the complex subquery adventure.