Re: SQL challenge--top 10 for each key value?

On Fri, 09 Apr 2004 02:11:44 -0400, Tom Lane wrote:

>> ROW_NUMBER() is a spec defined function. (6.10 of SQL200N)
>
> If the spec doesn't even have a year number yet, you can hardly expect
> real implementations to support it ;-)

SQL:2003 is finished. Among its new (non-core) OLAP features are a set of
"windows functions" (spec section 6.10), which include

feature ID T611 (elementary OLAP):
- ROW_NUMBER() OVER (...)
- RANK() OVER (...)
- DENSE_RANK() OVER (...)

feature ID T612 (extended OLAP):
- PERCENT_RANK() OVER (...)
- CUME_DIST() OVER (...)

See http://www.acm.org/sigmod/record/issues/0403/index.html#standards for
an article which summarizes the news in SQL:2003.

ROW_NUMBER() OVER may be used in queries where a PostgreSQL user which use
LIMIT.

RANK() OVER may be used in queries where a PostgreSQL user would have to
come up with a somewhat strange query in order to get acceptable
performance, see http://troels.arvin.dk/db/rdbms/#select-top-n-postgresql

--
Greetings from Troels Arvin, Copenhagen, Denmark