Clever Geek Handbook

Select the last entry of the day and related data

I have a table in SQLite:

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

I want to get the latest skip / failure information back 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 would like the select statement to return ...

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

Thanks in advance!

+2


source to share


3 answers


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
+6


source


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 crash for each day:

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


source


This is how I solve this issue:

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 column timestamp

has a single or primary key constraint. Otherwise (small chance) you might end up with more than one row per day.

0


source






More articles:


All Articles