如何同时对sql进行分组和排序?

本文介绍了如何同时对sql进行分组和排序?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下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.