Hi,

I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence'
method as a workaround and i think it at least gets the job done relatively
well, ... so no problems there.

Its just that from a usability point of view, isn't it better that we
provide some kind of an aliasing mechanism here that allows a new user to
(unknowingly but) implicitly use a temporary sequence rather than make him
use SubQuery with a COUNT(*) and a comparison operator (with disastrous
performance) instead ??

So for a new user :

A query such as this :

SELECT ROW_NUMBER() AS row_number , a, b, c
FROM table
WHERE table_id = 973
ORDER BY record_date;

is internally interpreted by the planner as :

CREATE TEMP SEQUENCE rownum;

SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
FROM (
SELECT a, b, c
FROM table
WHERE table_id = 973
ORDER BY record_date
) t;

DROP SEQUENCE rownum;


Any ideas ?
(Of what I remember, I think till recently PostgreSql internally replaced
'MAX(x)' queries with a 'ORDER BY x DESC LIMIT 1' implicitly)

--
Robins

I might be told off by some better SQL-User here on the list -
still here is my 2 Cents worth....

> I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence'
> method as a workaround and i think it at least gets the job done relatively

you use:

> CREATE TEMP SEQUENCE rownum;
> SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> FROM (
> SELECT a, b, c
> FROM table
> WHERE table_id = 973
> ORDER BY record_date
> ) t;

Doesn't this just return the 973th single record for the current
sequence order in your table?

I believe:
SELECT a, b, c FROM table
offset 973 limit 1

will accomplish the same result.


Stefan


--
email: stefan@yukonho.de
tel : +49 (0)6232-497631
http://www.yukonho.de

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi Stefan,

Well that was just an example. That table had a primary key on (id, date).
By bad then, coz I guess I should have clarified that earlier.

But what I meant from the example was that it is trivial for the parser to
automatically put an enveloping SELECT to add a ROW_NUMBER() field to any
user given query.

Regards,
Robins Tharakan

On 5/7/07, Stefan Becker <pgsql@yukonho.de> wrote:
>
> I might be told off by some better SQL-User here on the list -
> still here is my 2 Cents worth....
>
> > I needed ROW_NUMBER() in PostGresql and I did find the 'temporary
> sequence'
> > method as a workaround and i think it at least gets the job done
> relatively
>
> you use:
>
> > CREATE TEMP SEQUENCE rownum;
> > SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> > FROM (
> > SELECT a, b, c
> > FROM table
> > WHERE table_id = 973
> > ORDER BY record_date
> > ) t;
>
> Doesn't this just return the 973th single record for the current
> sequence order in your table?
>
> I believe:
> SELECT a, b, c FROM table
> offset 973 limit 1
>
> will accomplish the same result.
>
>
> Stefan