Sorting by date & time in descending order?

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...

8 Answers

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 

putting the UNIX_TIMESTAMP will do the trick.

SELECT id, NAME, form_id, UNIX_TIMESTAMP(updated_at) AS DATE FROM wp_frm_items WHERE user_id = 11 && form_id=9 ORDER BY DATE DESC 

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

"DESC" stands for descending but you need ascending order ("ASC").

SELECT * FROM ( 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 ) AS TEMP ORDER BY DATE(updated_at) DESC, name DESC 

Give it a try.

If you mean you want to sort by date first then by names

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,name ASC 

This will sort the records by date first, then by names

This is one of the simplest ways to sort record by Date:

SELECT `Article_Id` , `Title` , `Source_Link` , `Content` , `Source` , `Reg_Date`, UNIX_TIMESTAMP( `Reg_Date` ) AS DATE FROM article ORDER BY DATE DESC 

Following Query works for me. Database Tabel t_sonde_results has domain d_date (datatype DATE) and d_time (datatype TIME) The intention is to query for last entry in t_sonde_results sorted by Date and Time

select * from (select * from (SELECT * FROM t_sonde_results WHERE d_user_name = 'kenis' and d_smartbox_id = 6 order by d_time asc) AS tmp order by d_date and d_time limit 1) as tmp1

I used a simpler solution found partly here:
How to sort details with Date and time in sql server ?
I used this query to get my results:

SELECT TOP (5) * FROM My_Table_Name WHERE id=WhateverValueINeed ORDER BY DateTimeColumnName DESC

This is more straight forward and worked for me.

Notice: the column of the Date has the "datetime" type