Select first row in a group

Question

I'm using this query to get first data entry in an hour:

select * 
from
(   
    select time_stamp, value 
    from tbl 
    order by time_stamp desc
) 
group by strftime('%m%d%H',time_stamp) 
order by time_stamp;

It gives me proper results, but is quite slow with a lot of data points and limited hardware resources. When not sorting before GROUP it is much faster but then I get values from random parts of hour. Any suggestions?

Example returned values:

select ts, t1 from temperatura group by strftime('%m%d%H',ts) order by ts limit 5;
2013-12-22 09:59:01|22062
2013-12-22 10:59:02|21937
2013-12-22 11:59:02|21937
2013-12-22 12:57:02|22000
2013-12-22 13:59:02|21625

select * from(select ts, t1 from temperatura order by ts desc) group by strftime('%m%d%H',ts) order by ts limit 5;
2013-12-22 09:58:48|22000
2013-12-22 10:00:02|22000
2013-12-22 11:00:02|21937
2013-12-22 12:00:02|21937
2013-12-22 13:19:41|21812

As you can see I get different answers. The second one is what I want to get.

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

Unless I'm missing something in the question

SELECT top 10 time_stamp, value 
FROM 
(
    select time_stamp, value
    from tbl 
    order by time_stamp desc
)
group by strftime('%m%d%H',time_stamp)
order by time_stamp;

And the equivalent in SqLite:

How to get Top 5 records in SqLite?

And maybe ordering by twice isn't necessary

edited May 23 '17 at 12:28

Community

1
1

answered Dec 30 '13 at 22:59

skarson

13
3

A group by does an implicit sort. The sort in the subselect should be unnessecary as you suggested. – Vulcronos Dec 30 '13 at 23:19
Apparetnly it doesn't in SQLite3, as in example. Or I'm getting something wrong – maniek765 Dec 31 '13 at 08:43

score 0 · Answer 2 · answered Dec 31 '13 at 09:30

0

in SqLite, instead of top 10, use limit 10

SELECT ts, t1 
FROM 
(
    select ts, t1
    from temperatura 
    order by ts desc
)
group by strftime('%m%d%H',ts)
order by ts
Limit 10;

answered Dec 31 '13 at 09:30

Deepika Janiyani

1,487
9
17

Select first row in a group

2 Answers2