I love windows functions

... or whatever they call those functions that allow ranking and anyway working on "windows" of data (or sets inside sets or ...).

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).

Code:

select
bb.code
from
(
select
aa.code,
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
from
paging_test aa
) bb
where
bb.pages = 2

where 10 is the number of rows per page and 2 is the page you want to retrieve.

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:

Code:

select
emp_rk.emp_name,
emp_rk.emp_dept
from
(
select
rank() over (partition by emp_dept order by emp_name) rk,
emp_name,
emp_dept
from
employee
) emp_rk
where
emp_rk.rk <>     

Of course the trick is to partition by the set definer and order by whatever makes sense to you, even random ordering can be the solution.

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).

Code:

select
tts.temp_date_time,
tts.temp_value,
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
) delta_previous
from
test_temp_seq tts;

Partition by 1 actually means no partition at all, I'm partitioning by a constant ;)

The missing link ...

... between me and Firebird.
The Tracker, that's it, claims are it's up again but it doesn't work for me.
I'd love to open a feature request, or add my vote to an existing one, "role assignment to stored procedures".
Actually I'm after assignment of the rdb$admin role to a stored procedure, in order to give unprivileged users the ability to perform some administrative tasks without the need for sysdba.
I think that this solution is much better than adding the

... AS USER <...> PASSWORD <...> ROLE <...>

detailed in CORE-2452 : Add Role Name in input parameters for EXECUTE STATEMENT in the stored procedure code, because I don't want to hardcode passwords or such informations into stored procedures.
I don't think that changing a password should be able to break a stored procedure.