pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Troels Arvin <troels@arvin.dk>
To: pgsql-sql@postgresql.org
Subject: Re: SQL challenge--top 10 for each key value?
Date: Sun, 11 Apr 2004 00:13:17 +0200
Message-ID: <pan.2004.04.10.22.13.16.594061@arvin.dk> (raw)
References: <33af4fa4e4ec230574cefcff737ea40a@news.teranews.com>
	<87r7uyf3ki.fsf@stark.xeocode.com>
	<1081481466.56361.808.camel@jester>
	<21760.1081491104@sss.pgh.pa.us>

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





view thread (16+ messages)  latest in thread

Message-ID: <pan.2004.04.10.22.13.16.594061@arvin.dk>
Permalink:  ../pan.2004.04.10.22.13.16.594061@arvin.dk/
Also on:    postgresql.org/message-id/pan.2004.04.10.22.13.16.594061@arvin.dk

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: troels@arvin.dk
  Subject: Re: SQL challenge--top 10 for each key value?
  In-Reply-To: <pan.2004.04.10.22.13.16.594061@arvin.dk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox