How can I select rows where number went down as date went up

Question

If I have a program at a repair shop and I want to select all of the cars in my RepairOrder table where the mileage of the later repair order is less than the mileage of the prior repair order, how can I build that select 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 be selecting VehicleID 1 because it has a RepairDate that is greater then the previous row, but a Mileage that is less than the previous row. There could also be 3 rows with the same vehicle and the middle date has a mileage of 3 or 5000000, and I will need to select those VehicleID's as well.

Results from 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 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 show all columns so you can check what row have the problem.

Also I avoid using distinct because as OP suggest there may be several mistake for same car and this way you can see it all.