Sorting by date & time in descending order?

Question

all I want to display last 5 entered data for specific id. My sql query is,

SELECT id, name, form_id, DATE(updated_at) as date   FROM wp_frm_items   WHERE user_id = 11 && form_id=9   ORDER BY updated_at DESC 

updated_at is DATETIME

It displays last 5 entry sort by date not by time. On same date then it is sorting alphabetically.

Suppose i have 3 entries in same date with diff time

let's say

Ajay 1/3/2012 1:15 John 1/3/2012 1:00 Bony 1/3/2012 1:10 

after querying the above query

what i got is

Ajay 1/3/2012 1:15 Bony 1/3/2012 1:10 John 1/3/2012 1:00 

Sort by date then after alphabetically

What i want is this..

John 1/3/2012 1:00 Bony 1/3/2012 1:10 Ajay 1/3/2012 1:15 

Sorted by date and time also...

Answer 1

If you want the last 5 rows, ordered in ascending order, you need a subquery:

SELECT * FROM     ( SELECT id, name, form_id, DATE(updated_at) AS updated_date, updated_at       FROM wp_frm_items       WHERE user_id = 11          AND form_id=9       ORDER BY updated_at DESC       LIMIT 5     ) AS tmp ORDER BY updated_at 

---

After reading the question for 10th time, this may be (just maybe) what you want. Order by Date descending and then order by time (on same date) ascending:

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