How do I group and order with sql at the same time

问题:

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

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.

回答1:

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.

回答2:

You could do it as a self-join. In this case, I've called the table "table," substitute your own table name in:

SELECT
  test.Name,
  test.LastUpdated,
  test.Status
FROM
  test INNER JOIN 
    ( SELECT
        Name,
        MAX(LastUpdated) AS LatestUpdated
      FROM
        test
      GROUP BY
        Name ) AS latest
    ON test.Name = latest.name AND test.LastUpdated = latest.LatestUpdated;

Hope this helps!

回答3:

SELECT 
       t.Name, 
       (Select LastUpdated from [table] t1 where t.name = t1.name order by lastUpdated desc LIMIT 1) as LastUpdated, 
       (Select Status from [table] where t1.name = t.name order by lastUpdated desc LIMIT 1) as Status
FROM [table] t
GROUP by Name