Gavin,
I'm also interested in the topic, but right now I am wondering if
rank() function is a reserved name ? We're working on built-in
tsearch2 for 8.3 release and we already have rank() function.
Oleg
On Sun, 21 Jan 2007, Gavin Sherry wrote:
On Sat, 20 Jan 2007, Tom Lane wrote:
Gavin Sherry <[EMAIL PROTECTED]> writes:
We want to answer the following: for each employee: what is their rank in
terms of salary and what is their rank in terms of age. This query
answers that:
select empno, rank() over (order by salary) as srank,
rank() over (order by age) as arank
from employees order by empno;
Eeek. This seems like the worst sort of action-at-a-distance. How does
rank() know what value it's supposed to report the rank of?
This is a frustratingly inconsistent bit of the spec. Rank is defined as
follows:
RANK() OVER WNS is equivalent to:
( COUNT (*) OVER (WNS1 RANGE UNBOUNDED PRECEDING)
- COUNT (*) OVER (WNS1 RANGE CURRENT ROW) + 1 )
Say the salary column has the following values: {100, 200, 200, 300}. This
would give the following output: {1, 2, 2, 4}. DENSE_RANK() would give:
{1, 2, 2, 3}.
These functions are pretty ugly (if you think about them in terms of our
existing aggregates). However, they are by far the most heavily used
window functions (along with ROW_NUMBER()).
Thanks,
Gavin
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
