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

5

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

4

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.

3

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

0

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

1

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy