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

Can you add the table's definition? (so we see the datatypes) — ypercubeᵀᴹ, Mar 01 '12 at 06:58

ypercubeᵀᴹ · Accepted Answer · 2012-03-01T09:15:01.663

87

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

edited Mar 01 '12 at 09:15

answered Mar 01 '12 at 07:01

ypercubeᵀᴹ

113,259
19
174
235

1

If that is what he wants, much better to reverse the result in the application rather than do that query right? – Matthew Scragg Mar 01 '12 at 07:04
@MatthewScragg: With just 5 rows returned, I don't think it matters, both are ok. – ypercubeᵀᴹ Mar 01 '12 at 07:07
See Last 5 or 10 is not an issue here issue is short by time too – Ajay Patel Mar 01 '12 at 07:09
I don't clearly understand why we need to add DATE(datefield) to an DateTime field... but it worked! – Ari Waisberg Jun 23 '21 at 21:11

score 24 · Answer 2 · answered Mar 01 '12 at 11:24

24

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

answered Mar 01 '12 at 11:24

tariq

466
4
15

score 8 · Answer 3 · answered Mar 01 '12 at 06:57

8

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

answered Mar 01 '12 at 06:57

Māris Kiseļovs

16,957
5
41
48

Shakti Singh · Answer 4 · 2012-03-01T08:29:07.287

3

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.

edited Mar 01 '12 at 08:29

answered Mar 01 '12 at 06:57

Shakti Singh

84,385
21
134
153

1

@NoobASThreeDeveloper: No, it is not unnecessary. It is not only the date in the column, there is time also and in most of cases records can not have same date and time both. Therefore the step is needed – Shakti Singh Mar 01 '12 at 07:27
@Click: The step is indeed unnecessary. You don't need to wrap this in a subquery. A simple `ORDER BY DATE(updated_at) DESC, name DESC` would have the same result. Not to mention that the syntax you have is not valid SQL (despite that MySQL may allow it). In the external query, there is no `updated_at` and the `ORDER BY DATE(updated_at)` should result in an error. – ypercubeᵀᴹ Mar 01 '12 at 08:09
@ypercube: If you do this records will not be sorted by the `TIME` as well. OP wants records to be sorted by date and time both not only the date – Shakti Singh Mar 01 '12 at 08:17

score 2 · Answer 5 · edited Feb 14 '17 at 15:39

2

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

edited Feb 14 '17 at 15:39

answered Sep 11 '15 at 06:38

Amit Kumar

21
2

score 2 · Answer 6 · answered Mar 01 '12 at 07:07

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

score 0 · Answer 7 · answered May 19 '20 at 07:37

0

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

answered May 19 '20 at 07:37

dagnolof

1

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 `t_sonde_results` WHERE d_user_name = 'kenis' and d_smartbox_id = 6 order by d_date desc, d_time desc limit 1 – dagnolof May 20 '20 at 22:33

score -2 · Answer 8 · edited Apr 23 '18 at 13:46

-2

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

edited Apr 23 '18 at 13:46

soundslikeodd

1,078
3
19
32

answered Apr 23 '18 at 13:22

Saadat

461
6
9

Sorting by date & time in descending order?

8 Answers8

Linked

Related