efficiency of a 'simple script'

I am trying to retrieve from one table, 'car' which holds annual year and mileage details of many cars, the car which has covered the most miles between two years e.g. between 1995 and 2000. I can't find a more efficient way than this, tho I'm sure there is one, perhaps using 'having' and group by : select p.car, (p.miles - p1.miles) as miles from car p, car p1 where p.yr = 2000 and p1.yr = 1995 and p.car = p1.car and miles = (select max(p.miles - p1.miles) from car p, car p1 where p.yr = 2000 and p1.yr = 1995 and p.car = p1.car )! It seems that I'm missing an easier way !

ConnorsJohnny wrote: > > I am trying to retrieve from one table, 'car' which holds annual year and > mileage details of many cars, the car which has covered the most miles > between two years e.g. between 1995 and 2000. > I can't find a more efficient way than this, tho I'm sure there is one, > perhaps using 'having' and group by : > > select p.car, (p.miles - p1.miles) as miles > from car p, car p1 > where p.yr = 2000 and p1.yr = 1995 > and p.car = p1.car> > and miles = (select max(p.miles - p1.miles) > from car p, car p1 > where p.yr = 2000 and p1.yr = 1995 > and p.car = p1.car )! > > > It seems that I'm missing an easier way ! Beware the car bought in 1996 that's done 400,000 miles so far! Also, is the value in car.miles the odometer reading or the miles travelled during the year? If it is the odometer reading, then the subtraction is OK; if it is the miles travelled in the year, then you need a sum of the values in distinct years rather than the difference. And if you use the odometer reading, don't you need to subtract the end of 1994 reading from the end of 1999 (or 2000) reading to get the miles travelled in 1995 counted too? Depending on context, you might sort the basic SELECT statement by the miles column in reverse order, possibly with a hint that you're interested in the first rows, and then use a cursor to pick off just the first row or rows (in case there is more than one car with the same maximum mileage). SELECT p1.car, (p2.miles - p1.miles) as mileage FROM car p1, car p2 WHERE p1.yr = 2000 AND p2.yr = 1995 AND p1.car = p2.car ORDER BY mileage DESC; -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"