Descending Order Date Format mysql

September 23, 2022 . 1 MIN READ

If you need to retrieve records where the most recent days appear first, but the specific entries within those days are listed in chronological order (earliest to latest), you can use a dual-layer ORDER BY clause.

This approach ensures that your primary focus is on the newest dates, while maintaining a logical “start-to-finish” flow for the events occurring on those dates.

The Optimized Query

SQL

SELECT 
    id, 
    name, 
    form_id, 
    DATE(updated_at) AS updated_date
FROM wp_frm_items
WHERE user_id = 11 
  AND form_id = 9
ORDER BY 
    DATE(updated_at) DESC, 
    updated_at ASC;

Why this works:

DATE(updated_at) DESC: This groups your results by day, putting today’s entries at the top of the list.
updated_at ASC: This looks at the full timestamp (including hours, minutes, and seconds) to sort the items within that specific day from oldest to newest.

Reference:

https://stackoverflow.com/questions/9511882/sorting-by-date-time-in-descending-order