SQL Query that compares data between instances

[#1]

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??

[#2]

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.

[#3]

Thanks for the join suggestion. It took me a bit to figure out the exact scripting for my database, but I was able to make it work and got exactly the results I was looking for.

Thanks again!