Mysql selects different values from a series
I have a mysql table like
Speed Lat Lon TimeStamp
12 1 22 2012-02-03 00:00:05
13 2 23 2012-02-03 00:00:10
0 3 24 2012-02-03 00:00:15
0 3 24 2012-02-03 00:00:20
14 4 25 2012-02-03 00:00:25
0 5 26 2012-02-03 00:00:30
0 5 26 2012-02-03 00:00:35
0 5 26 2012-02-03 00:00:40
5 6 27 2012-02-03 00:00:45
0 6 27 2012-02-03 00:00:50
I need to select (all records with speed! = 0) + (one record from a series of records where speed == 0). those. i will expect something like
Speed Lat1 Lat2 Timestamp
12 1 22 2012-02-03 00:00:05
13 2 23 2012-02-03 00:00:10
0 3 24 2012-02-03 00:00:15
14 4 25 2012-02-03 00:00:25
0 5 26 2012-02-03 00:00:30
5 6 27 2012-02-03 00:00:45
0 6 27 2012-02-03 00:00:45
I've tried using different and grouping for different columns .. Doesn't work .. Please help me with my query.
Edited:
I added a timestamp field and another record that I use for ORDER BY If I use a group using Lat, the last Lon record will not be selected. But I need it. (The thing is, the car returns to the same latitude and longitude at a different time, whether it is moving or not) Any help would be appreciated.
source to share
Not sure what you want if you have nonzero speed but duplicate lines (i.e. two lines of 13, 2, 23)? Without considering the point above, you can get the expected results using UNION
DEMO (thanks @echo_me for the fiddle)
Select Speed , lat as Lat1 , Lon as Lon1
From Table1 where speed <> 0
UNION
Select Speed , lat as Lat1 , Lon as Lon1
From Table1 where speed = 0
Order by lat1
Or if you want to duplicate non- UINON ALL
null entries you could with DEMO grouped null entries
Select Speed , lat as Lat1 , Lon as Lon1
From Table1 where speed <> 0
UNION ALL
Select Speed , lat as Lat1 , Lon as Lon1
From Table1 where speed = 0
Group by speed,lat1,lon1
Order by lat1
source to share