MYSQL Select first rows after specified

Question

I have table with positions of car (from gps tracker) and I want to select positions of start and end of drive. So I should select first row where speed >0 and last where speed was >0 with specific date (day). How can I do this?

For example I have table with values:

+------+-------+---------------------+
| id   | speed | date                |
+------+-------+---------------------+
|    1 |     0 | 2015-02-03 10:00:00 |
|    2 |    10 | 2015-02-03 10:00:10 |
|    3 |    50 | 2015-02-03 10:00:20 |
|    4 |   100 | 2015-02-03 10:00:30 |
|    5 |    50 | 2015-02-03 10:01:00 |
|    6 |     0 | 2015-02-03 10:01:10 |
|    7 |    10 | 2015-02-03 12:00:10 |
|    8 |    50 | 2015-02-03 12:00:20 |
|    9 |   100 | 2015-02-03 12:00:30 |
|   10 |    50 | 2015-02-03 12:01:00 |
|   11 |     0 | 2015-02-03 12:01:10 |
+------+-------+---------------------+

And I need to select records with ids: 2,5,7,11

EDIT

This is the full list of columns : id (identity), car_id,altitude,latitude,longitude,speed,date

I need to calculate each track distance. (one car may drive many times in 1 day - it can drive from 6 am to 10 am, then stop on parking from 10 am to 1 pm and then again drive from 1pm to 5 pm.) So first I need to "detect" start and stop of driving (car also send data to database when it keeps on parking (speed=0) ),then calculate distance using something like this distance calculations in mysql queries

This is the database from http://www.traccar.org/ project.

I don't understand the question can you try to be more specific? What is your desired output? Can you show an example of what you have tried so far? — Cyclonecode, Feb 19 '15 at 11:36

score 0 · Answer 1 · answered Feb 19 '15 at 11:39

If your ID grows over time then you can use two sub queries (without having to use HAVING):

SELECT * FROM mytable 
WHERE id IN (
   SELECT MIN(id) FROM mytable
   WHERE speed > 0
   GROUP BY DAY(date)
)
OR id IN (
   SELECT MAX(id) FROM mytable
   WHERE speed > 0
   GROUP BY DAY(date)
)

The first sub query selects the MIN(id) per day, the second the MAX(id) per day. In both cases only rows with speed > 0 are returned. This should get you the first ID for each day with speed > 0 and the last one.

If you do not want to rely on the IDs being sorted by date, you have to use HAVING date = MIN(date) instead of MIN(id) (the same goes for MAX(id)).

Of course, date is a reserved keyword, so you probably have to escape it.

score 0 · Answer 2 · answered Feb 19 '15 at 12:15

Wouldn't it be easier to add a column like car_id which relates each record to a car:

+------+--------+-------+---------------------+
| id   | car_id | speed | date                |
+------+--------+-------+---------------------+
|    1 |      1 |     0 | 2015-02-03 10:00:00 |
|    2 |      1 |    10 | 2015-02-03 10:00:10 |
|    3 |      1 |    50 | 2015-02-03 10:00:20 |
|    4 |      1 |   100 | 2015-02-03 10:00:30 |
|    5 |      1 |    50 | 2015-02-03 10:01:00 |
|    6 |      2 |     0 | 2015-02-03 10:01:10 |
|    7 |      2 |    10 | 2015-02-03 12:00:10 |
|    8 |      2 |    50 | 2015-02-03 12:00:20 |
|    9 |      2 |   100 | 2015-02-03 12:00:30 |
|   10 |      2 |    50 | 2015-02-03 12:01:00 |
|   11 |      3 |     0 | 2015-02-03 12:01:10 |
+------+--------+-------+---------------------+

Then you could get the desired rows by using a query like:

SELECT MAX(id) id, car_id, MAX(`date`) `date` FROM <table> 
WHERE speed > 0 
GROUP BY car_id 
ORDER BY speed ASC;

+------+--------+---------------------+
| id   | car_id | date                |
+------+--------+---------------------+
|    5 |      1 | 2015-02-03 10:01:00 |
|   10 |      2 | 2015-02-03 12:01:00 |
+------+--------+---------------------+

MYSQL Select first rows after specified

2 Answers2