Previous and Next row record

Question

I have following output

ID   Number   Speed   LAT           LONG           DATETIME
101   AB01      15    73.066016     33.5840768     9/1/15 23:10
101   AB01      20    73.0619712    33.5871456     9/1/15 23:30
101   AB01      0     73.0722176    33.6007488     9/1/15 23:45
101   AB01      0     73.0722112    33.6007488     9/2/15 02:10
101   AB01      0     73.0722176    33.6007008     9/2/15 02:35
101   AB01      0     73.0722432    33.6007456     9/2/15 04:35
101   AB01      0     73.0721664    33.6007904     9/3/15 12:35
101   AB01      0     73.072192     33.6007488     9/3/15 13:35
101   AB01      0     73.072192     33.6007488     9/4/15 11:35
101   AB01      0     73.072192     33.6007488     9/4/15 14:35
101   AB01      1     73.072192     33.6007488     9/5/15 14:35

but required output are

ID   Number   Speed   LAT           LONG           DATETIME
101   AB01      15    73.066016     33.5840768     9/1/15 23:10
101   AB01      20    73.0619712    33.5871456     9/1/15 23:30
101   AB01      0     73.0722176    33.6007488     9/1/15 23:45   
101   AB01      0     73.072192     33.6007488     9/4/15 14:35
101   AB01      1     73.072192     33.6007488     9/5/15 14:35

i want to skip extra 0 speed value.if i include these 0 zero's query response time increase. code attempt

SELECT   Distinct ID,Number,Speed,LAT,LONG,DATETIME
FROM        table
group by D,Number,Speed,LAT,LONG,DATETIME

What is your problem? What did you try? How do your recognize the records you want to filter and want to keep? — Edwin Stoteler, Sep 21 '15 at 06:39
@EdwinStoteler Speed 0 mean Vehicle does't change their location.So i want location and last stop location. — Doe, Sep 21 '15 at 06:44

score 4 · Answer 1 · edited Sep 21 '15 at 07:25

You can use LEAD, LAG window functions to detect speed discontinuities (if this is what you actually want):

SELECT ID, Number, Speed, LAT, [LONG], [DATETIME]
FROM (
  SELECT ID, Number, Speed, LAT, [LONG], [DATETIME],
         LEAD(Speed) OVER (PARTITION BY ID, Number
                           ORDER BY [DATETIME]) AS NextSpeed,
         LAG(Speed) OVER (PARTITION BY ID, Number
                           ORDER BY [DATETIME]) AS PrevSpeed         
  FROM mytable) AS t
WHERE Speed <> COALESCE(NextSpeed, -1) OR Speed <> COALESCE(PrevSpeed, -1)

Demo here

Previous and Next row record

1 Answers1