Thread: ROW_NUMBER alias

Hi,<br /><br />I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence' method as a workaround and i
thinkit at least gets the job done relatively well, ... so no problems there. <br /><br />Its just that from a
usabilitypoint 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
comparisonoperator (with disastrous performance) instead ?? <br /><br />So for a new user :<br /><br />A query such as
this:<br /><br />    SELECT ROW_NUMBER() AS row_number , a, b, c<br />        FROM table<br />        WHERE table_id  =
973<br/>        ORDER BY record_date;<br /><br />is internally interpreted by the planner as : <br /><br />    CREATE
TEMPSEQUENCE rownum; <br /><br />    SELECT nextval('rownum') AS row_number , t.a, t.b, t.c<br />    FROM (<br />   
   SELECT a, b, c<br />        FROM table<br />        WHERE table_id  = 973<br />        ORDER BY record_date<br />   
)t;<br /><br />    DROP SEQUENCE rownum;<br /><br />   <br clear="all" />Any ideas ?<br />(Of what I remember, I think
tillrecently PostgreSql internally replaced 'MAX(x)' queries with a 'ORDER BY x DESC LIMIT 1' implicitly)<br /><br
/>--<br/>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 tableoffset 973 limit 1 

will accomplish the same result.


Stefan


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

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