MySQL 按日期和时间降序排序？

Sorting by date & time in descending order?

提问by Ajay Patel

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

我只想显示特定 id 的最后 5 个输入数据。我的 sql 查询是，

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_atis DATETIME

updated_at是日期时间

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

它显示按日期而不是按时间排序的最后 5 个条目。在同一日期，然后按字母顺序排序。

Suppose i have 3 entries in same date with diff time

假设我在同一日期有 3 个条目，但时间不同

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

还按日期和时间排序...

回答by ypercube??

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

如果你想要最后 5 行，按升序排列，你需要一个子查询：

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:

在第 10 次阅读问题后，这可能是（可能）您想要的。按日期降序排序，然后按时间（同一日期）升序排序：

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

回答by tariq

putting the UNIX_TIMESTAMP will do the trick.

放置 UNIX_TIMESTAMP 就可以了。

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

回答by Māris Kise?ovs

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

“DESC”代表降序，但您需要升序（“ASC”）。

回答by Shakti Singh

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.

试一试。

回答by dejjub-AIS

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

这将首先按日期对记录进行排序，然后按名称排序

回答by Amit Kumar

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 

回答by dagnolof

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

以下查询对我有用。数据库表 t_sonde_results 有域 d_date（数据类型 DATE）和 d_time（数据类型 TIME） 目的是查询 t_sonde_results 中按日期和时间排序的最后一个条目

select * from (select * from (SELECT * FROM t_sonde_resultsWHERE 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

select * from (select * from (SELECT * FROM t_sonde_resultsWHERE 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

回答by Saadat

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:

我在这里使用了一个更简单的解决方案：
How to sort details with Date and time in sql server ?
我使用这个查询来得到我的结果：

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

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

注意：Date 列的类型为“datetime”