select latest entry per day and corresponding data

Question

I have a table in SQLite:

CREATE TABLE test_results(timestamp TEXT, npass INTEGER, nfails INTEGER)

I want to return the last pass/fail information for each day. For example if the table contains...

2009-08-31 23:30:19|0|24
2009-08-31 23:37:18|0|24
2009-08-31 23:40:00|0|24
2009-09-01 19:02:13|0|2
2009-09-01 19:08:24|2|0
2009-09-01 19:20:29|2|0

I'd like a select statement to return...

2009-08-31 23:40:00|0|24
2009-09-01 19:20:29|2|0

Thanks in advance!

Answer 1

You can try this query:

SELECT * FROM (select * from test_results ORDER BY timestamp)
GROUP BY date(timestamp)

If you also want to get the first fail for each day:

SELECT * FROM (select * from test_results ORDER BY timestamp DESC)
GROUP BY date(timestamp)

Answer 2

Try:

SELECT t.timestamp,
       t.npass,
       t.nfails
  FROM TEST_RESULTS t
  JOIN (SELECT MAX(tt.timestamp) 'maxtimestamp'
         FROM TEST_RESULTS tt
     GROUP BY date(tt.timestamp)) m ON m.maxtimestamp = t.timestamp

Answer 3

This is how I solve this type of question:

SELECT t1.*
FROM test_results t1
LEFT OUTER JOIN test_results t2
  ON (DATE(t1.timestamp) = DATE(t2.timestamp) AND t1.timestamp < t2.timestamp)
WHERE t2.timestamp IS NULL;

This assumes that the timestamp column has a unique or primary key constraint on it. Otherwise there's a (small) chance you could get more than one row per day.