本文介绍了如何同时对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.
更多推荐
如何同时对sql进行分组和排序?
发布评论