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.
Row Machine Date Mileage
1 1 1/1/2009 11906
2 2 2/2/2009 10753
3 1 4/1/2009 28097
4 2 5/1/2009 25047
5 1 6/1/2009 26007
6 2 7/1/2009 37095
7 1 11/1/2009 52956
8 2 12/1/2009 51093
I understand basic SQL query logic, but I don't know if there is a way to compare one rows data to another? Is there a special operator that would signal this comparison??