如何同时对sql进行分组和排序? [英] How do I group and order with sql at the same time?

问题描述

我有以下sqlite3表:

I have got the following sqlite3 table:

Name | LastUpdated | Status
============================
Adam | 2011-05-28  | 1
Bob  | 2011-05-05  | 6
Adam | 2011-05-27  | 2
Adam | 2011-05-16  | 1
Adam | 2011-05-26  | 3
Bob  | 2011-05-18  | 1
Adam | 2011-05-29  | 6

，我想按LastUpdated列的顺序在每个Name中选择一行.所以我想获取这些数据:

and I want to select the a row per Name ordered by the LastUpdated column. So I want to get this data:

Adam | 2011-05-29  | 6
Bob  | 2011-05-18  | 1

我认为我必须做一个子查询，但是我不知道该怎么做.

I think I have to do a subquery, but I can't figure out how to go about it.

推荐答案

SQLite(和MySQL)支持:

SQLite (and MySQL) support:

  SELECT t.name, 
         MAX(t.lastupdated), 
         t.status 
    FROM [table] t 
GROUP BY t.name

但是大多数其他数据库将要求您使用:

But most other databases would require you to use:

SELECT a.name, a.lastupdate, a.status
  FROM YOUR_TABLE a
  JOIN (SELECT t.name, MAX(t.lastupdated) AS max_lastupdated
          FROM YOUR_TABLE t
      GROUP BY t.name) b ON b.name = a.name
                        AND b.max_lastupdated = a.lastupdated

...但是，如果一个名称具有多个具有相同最高日期值的记录，则将返回重复项.

...though this will return duplicates if a name has more than one record with the same highest date value.

这篇关于如何同时对sql进行分组和排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！