-3

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 
Doe
  • 13
  • 4

1 Answers1

4

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

Edwin Stoteler
  • 1,218
  • 1
  • 10
  • 25
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98