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

help / color / mirror / Atom feed

ROW_NUMBER alias
3+ messages / 2 participants
[nested] [flat]

* ROW_NUMBER alias
@ 2007-05-06 16:01  Robins <tharakan@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Robins @ 2007-05-06 16:01 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ROW_NUMBER alias
@ 2007-05-06 18:41  Stefan Becker <pgsql@yukonho.de>
  parent: Robins <tharakan@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Stefan Becker @ 2007-05-06 18:41 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ROW_NUMBER alias
@ 2007-05-07 02:03  Robins <tharakan@gmail.com>
  parent: Stefan Becker <pgsql@yukonho.de>
  0 siblings, 0 replies; 3+ messages in thread

From: Robins @ 2007-05-07 02:03 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Stefan Becker <pgsql@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

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2007-05-07 02:03 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-06 16:01 ROW_NUMBER alias Robins <tharakan@gmail.com>
2007-05-06 18:41 ` Stefan Becker <pgsql@yukonho.de>
2007-05-07 02:03   ` Robins <tharakan@gmail.com>

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