How can I select rows whose number decreases as the date grows

Question

If I have a program in a repair shop and I want to select all the cars in my RepairOrder table, where the mileage of a later repair order is less than the mileage of a previous repair order, how can I create this selection statement?

ID VehicleID Mileage RepairDate 01 1 18425 2013-08-13 02 1 28952 2013-02-26 03 2 22318 2012-08-27 04 3 21309 2012-08-07 05 3 16311 2012-02-27 06 3 16310 2012-02-11 07 4 11098 2011-03-23 08 5 21309 2012-08-07 09 5 16309 2012-02-27 10 5 16310 2012-02-11 

In this case, I should only select VehicleID 1, because it has a RepairDate that is larger than the previous row, but mileage that is less than the previous row. There may also be 3 rows with the same car, and the average date has a mileage of 3 or 5,000,000, and I will also need to select this VehicleID.

Results of using the LEAD () function

 ID RepairDate Mileage 25 2011-12-23 45934 48 2009-02-26 13 48 2009-04-24 10 71 2011-07-26 31163 71 2015-01-13 65656 

Answer 1

This is a great place to use the LEAD () function for sql 2014 +

SQL FIDDLE DEMO

 WITH NextM as ( SELECT * , LEAD(Mileage, 1, null) over (partition by VehicleID order by RepairDate) NextMileage FROM RepairOrder ) SELECT * FROM NextM WHERE Mileage > NextMileage 

My solution displays all the columns so you can check which row has the problem.

I also avoid using distinct , because, as the OP suggests, there may be several errors for one car, and so you can see it all.

Answer 2

It is not very effective, but you can make pairwise choices

 select t1.VehicleID from table t1, table t2 where t1.VehicleId = t2.VehicleId AND t1.Mileage > t2.Mileage AND t1.RepairDate < t2.RepairDate 

Most likely, the best solution, since paired samples get EXTREMELY SLOW, but this should work as it is.

Answer 3

 select distinct RO.VehicleID from RepairOrder RO where exists(select * from RepairOrder where ID != RO.ID and VehicleID = RO.VehicleID and RepairDate > RO.RepairDate and Mileage < RO.Mileage); 

Answer 4

 WITH RepairSeqs AS( SELECT DateSeq = Row_Number OVER (PARTITION BY VehicleID ORDER BY RepairDate), MileageSeq = Row_Number OVER (PARTITION BY VehicleID ORDER BY Mileage), * FROM dbo.RepairOrder ) SELECT * FROM RepairSeqs WHERE DateSeq <> MileageSeq; 

Answer 5

 select distinct t.VehicleId from ( select t.*, LEAD(Mileage) OVER (Partition by VehicleId ORDER BY RepairDate) LeadMileageValue from RepairOrder t ) t where t.Mileage > t.LeadMileageValue