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
Ditiris
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
OMG Ponies
source
to share
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
Nick dandoulakis
source
to share
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
Bill karwin
source
to share