1

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.

Rudacles
  • 183
  • 1
  • 1
  • 6
  • Doesn't makes sense - the bob record you want has the earliest date; Adam's is the latest... – OMG Ponies May 31 '11 at 02:54
  • I just figured it out. SELECT Name, MAX(LastUpdated), Status FROM [table] GROUP BY Name – Rudacles May 31 '11 at 03:00
  • doing it that way, you will get an arbitrary status field, not the one that corresponds to the MAX(LastUpdated) column - if that is what you wanted. In your examples, you showed the 'status' field with 'Bob' as '1', which is the status field for the latest updated date... – M.R. May 31 '11 at 03:14

3 Answers3

3

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    What if they have different status, but same date? Also, you're not aggregating on status - wouldn't that throw an error? I think the requirement was to only group on the name, and select latest of everything else based on the date (or as I understood it).. – M.R. May 31 '11 at 03:07
  • @M.R.: SQLite (and MySQL) pick an arbitrary row - http://www.sqlite.org/lang_select.html#resultset - for columns that are not withing aggregate functions or the group by. It's actually part of the ANSI standard. – OMG Ponies May 31 '11 at 03:09
  • But you don't want that, right? Wasn't the requirement to pick the latest 'status' as well, per the lastUpdatedDate field? Because for the row that has 'Bob', he has shown the status field that has the latest date... – M.R. May 31 '11 at 03:10
  • @M.R.: It's up to the OP, not me to determine what is right. It's the OP's comment to the question saying it solved their issue. – OMG Ponies May 31 '11 at 03:12
  • OK - but I would never leave that to be arbitrary... not sure what purpose it serves based on the group by... – M.R. May 31 '11 at 03:15
1

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!

King Skippus
  • 3,801
  • 1
  • 24
  • 24
0

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
M.R.
  • 4,737
  • 3
  • 37
  • 81
  • There's no correlation, so that will return the most recent status & lastupdated of all the records. Why you'd use a subselect instead of MAX, I don't know... – OMG Ponies May 31 '11 at 03:03
  • duh... edited it. I could have used MAX as well ... first instinct just appeared to be subselect :/ – M.R. May 31 '11 at 03:06