Archive for May 11th, 2009
PostgreSQL: emulating ROW_NUMBER
Comments enabled. I *really* need your comment
Note: this article concerns PostgreSQL 8.3 and below.
PostgreSQL 8.4 introduces window functions.
Window function ROW_NUMBER() implements the functionality in question more efficiently.
In one of the previous articles:
, I described emulating Oracle's pseudocolumn ROWNUM in PostgreSQL.
Now, we'll extend this query to emulate ROW_NUMBER.
A quick reminder: ROW_NUMBER is an analytical function in ANSI SQL 2003 supported by Oracle and MS SQL Server.
It enumerates each row in a resultset, but, unlike ROWNUM, may take two additional parameters: PARTITION BY and ORDER BY.
PARTITION BY splits a rowset into several partitions, each of them being numbered with its own sequence starting from 1.
ORDER BY defines the order the rows are numbered within each partition. This order may differ from the order the rows are returned in.
This function helps building queries which allow to select N rows for each partition.
Let's create a sample table and see how we do it in PostgreSQL:
Read the rest of this entry »
Subscribe in a reader