A couple of questions on Devshed forums I recently answered using those functions.
First one is about recordset paging (well, there are hundred of ways to do recordset paging, just google, but ayway) LIMIT being one of them, stored procedures another one (and ugly IMHO).
rank() over( partition by 1 order by aa.code) row_num,
trunc(((rank() over( partition by 1 order by aa.code))-1)/10)+1 pages
bb.pages = 2
In this case the rank() is over the entire set (note the partition clause) basically turning it into a row number.
The other one about finding two (top, random, whatever) elements inside sets (i.e. two employees per department).
In this case the OP has already got the fact that LIMIT is not enough for solving the problem cleanly, so I gave him a little example:
rank() over (partition by emp_dept order by emp_name) rk,
Unfortunately only PostgreSQL currently supports those functions, Firebird should when v3.0 will be out, MySQL doesn't seem too interested, they already gave us the LIMIT clause.
EDIT: another one here.
This time is about accessing the ever elusive "previous row", the solution is using the lag() function, note that a lead() function exists and that both can be defined over the entire ordered table or over sets defined by user through the partition by clause (as the functions described above).
lag(tts.temp_value) over (partition by 1 order by tts.temp_date_time),
tts.temp_value - coalesce(
lag(tts.temp_value) over (partition by 1 order by tts.temp_date_time), 0