How to compare integers and dates on current and next records

gistfile1.txt

I have a table of petrol purchases, each recording the current odometer reading:

id  | odometer  | user_id | created	
-------------------------------
2   | 63446     | 1       | 2012-01-15 11:27:57
4   | 63835     | 1       | 2012-01-19 17:09:37
5   | 64145     | 1       | 2012-01-28 13:02:08

I want to return each row along with a distance and days lasted for each record (comparing the current record with the next record).

It only uses a subquery to sort, I don't think you'd be able to do it without doing it in descending order. You could always iterate backwards through the resultset in the programming language of your choice, but you wouldn't be doing it with a query like this anyway if your app was going to be especially read heavy (you'd have denormalised it to pre-compute the difference at insert time, or you'd have thrown all manner of varniredimongos at it first).

Yeah I meant iterating over backwards. Though I would have probably just done it client side or even just put all the user's entries in one mongo document (since I'm learning that at the moment) and it seems like that would be easier to maintain and scale. Not that any of this matters unless it gets a lot of users.

Thanks for both your feedback on this. I'm conscious of performance and optimisation but realise, at this stage, it's not crucial.

Out of curiosity, what would you negate as large volumes: 10k, 100k or more records?

Pretty much the point the entire dataset and indexes don't fit in memory anymore.