Andrew,
In the past, I've had to do queries of the sort you mention. I've used a query like the following to do what you describe:
SELECT today.id, today.price AS todays_price,
yesterday.price AS yesterdays_price,
(today.price - yesterday.price) AS price_diff,
DECODE( yesterday.price, 0, NULL,
today.price/yesterday.price) as pct_change,
FROM stocks today, stocks yesterday
WHERE today.id = yesterday.id
AND TRUNC(today.day_date) = TRUNC(:incoming_date)
AND TRUNC(yesterday.day_date) = TRUNC(:incoming_date) - 1
AND today.id = :incoming_id
The values prefixed with colons are the input values (bind variables). If I were writing it for a production system, I'd modify the yesterday.day_date comparison to look for the most recent date before the :incoming_date rather than simply subtracting a day. The DECODE will deal with the possibility of a division by zero. There are some other things I'd consider doing, but it'd very dependent on what I needed to for the particular application. In any case, it sure seems cleaner than the rownum version. I must also say that I didn't try the above query directly (it's from memory), but I think the spirit of it is right.
I'd also be very surprised if Postgres couldn't do this query, making only minor changes to function names.
