CODEWITHSUNDEEP
androidsqlite
Mr Chops
Mr Chops

Reputation: 191

SELECT to contain values from first and last rows for each ID

I'm trying to come up with a database query and I'm really struggling, not only to get it working but to find a similar query I can base it on. I'm quite new to SQLite in Android development.

Here's what I'm trying to do. I have a table where I'm logging GPS locations. Each row has a pointId, routeId, latitude, longitude and datetime.

Each row in my results set needs to include the datetime of the first and last record for each routeId, which I've got now, but I also need to include the latitude and longitude of the first and last records.

Here's where I've got to so far, after removing several versions with joins and subqueries that didn't work: http://www.sqlfiddle.com/#!7/47012/69 (note the values are not real)

What I'd like to get to is:

routeid  minDatetime           maxDatetime          firstLat  firstLong   lastLat   lastLong
54       2014-02-16 12:01:00   2014-02-16 12:06:00  0.0004    1.345       0.0007    1.349
55       2014-02-16 12:07:00   2014-02-16 12:07:00  0.0011    1.388       0.0011    1.388

I hope this makes sense. Any help appreciated.

Upvotes: 2

Views: 70

Answers (2)

Mr Chops
Mr Chops

Reputation: 191

I managed to come up with a working query which, from running against my limited test records, does return what I'm after although until I get this into my app I'm not 100% sure it's right.

http://www.sqlfiddle.com/#!7/47012/147

My query, which no doubt could be improved upon:

select 
a.routeId, 
min(a.datetime),
max(a.datetime),
(select latitude from GPSPointLog b 
  WHERE b.routeId = a.routeId ORDER BY datetime LIMIT 1) 'firstLat',
(select longitude from GPSPointLog c 
  WHERE c.routeId = a.routeId ORDER BY datetime LIMIT 1) 'firstLong',
(select latitude from GPSPointLog d 
  WHERE d.routeId = a.routeId ORDER BY datetime DESC LIMIT 1) 'lastLat',
(select longitude from GPSPointLog e 
  WHERE e.routeId = a.routeId ORDER BY datetime DESC LIMIT 1) 'lastLong'
from GPSPointLog a 
GROUP BY a.routeId
order by a.routeId

I knew I was wrong going down the JOIN route.

Upvotes: 1

Simon
Simon

Reputation: 14472

You can take advantage of ORDER BY and LIMIT

SELECT * FROM <yourtable> WHERE routeid = '54' ORDER BY dateTime DESC LIMIT 1

to get the first one and;

SELECT * FROM <yourtable> WHERE routeid = '54' ORDER BY dateTime ASC LIMIT 1

to get the last.

Logic. Select rows where the id matches the one you are interested in, order them by dateTime, either descending or ascending, and return the first one.

Upvotes: 1

Related Questions