CODEWITHSUNDEEP
mysqlsql
Ajay Patel
Ajay Patel

Reputation: 5418

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

Upvotes: 54

Views: 459121

Answers (8)

dagnolof
dagnolof

Reputation: 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 (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

Upvotes: 0

Saadat
Saadat

Reputation: 521

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

Upvotes: -2

Amit Kumar
Amit Kumar

Reputation: 21

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

Upvotes: 2

tariq
tariq

Reputation: 466

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

Upvotes: 24

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

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

Upvotes: 87

Shakti Singh
Shakti Singh

Reputation: 86346

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.

Upvotes: 3

dejjub-AIS
dejjub-AIS

Reputation: 1541

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

Upvotes: 2

Māris Kiseļovs
Māris Kiseļovs

Reputation: 17275

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

Upvotes: 8

Related Questions