I am trying to build a SQL query that will enable me to identify discrepancies in my data by comparing data between instances. For example, given a machine number, a date, and a mileage, can I identify entries where the machine number is identical, the date of one entry is greater that a previous entry, but the mileage has decreased?
Ex: From the datatable below, I want my query to return only rows 3 and 5 because the mileage on machine 1 has decreased from the prior dated entry.
You can join a table to itself and get that result. From the sample data you showed, it would be something like this (depending on what exact flavor of SQL you are running on) - if the table name was MachineMileage:
Select * From MachineMileage as mm1 Join MachineMileage as mm2 on mm1.Machine = mm2.machine and mm1.Date < mm2.Date and mm1.Mileage > mm2.Mileage
May not have gotten that exactly right as I did it pretty quickly, but hopefully gets you in the right direction.