Re: placing position numbers on a query output

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: placing position numbers on a query output
Date: 2007-04-12 09:48:42
Message-ID: 20070412094842.GB29820@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

am Wed, dem 11.04.2007, um 16:50:17 +0100 mailte Oliveiros Cristina folgendes:
> Hello, All,
>
> I would like to know if Postgres has a function similar to ROW_NUMBER().
> I would like that the output of a query looked something like this
>
> column1 column2
> 1 x1 x2
> 2 y1 y2
> 3 z1 z2
> ...
>
> I suspect that my question has an easy and quick answer, but from my searches I
> couldn't find it yet :-(

You can do this either in the client-app or, for instance, with a
sequence like this:

test=# create table test (a text);
CREATE TABLE
test=*# insert into test values ('a');
INSERT 0 1
test=*# insert into test values ('c');
INSERT 0 1
test=*# insert into test values ('b');
INSERT 0 1
test=*# commit;
COMMIT
test=# create sequence s_test;
CREATE SEQUENCE
test=*# select nextval('s_test'), a from (select a from test order by 1)
foo;
nextval | a
---------+---
1 | a
2 | b
3 | c
(3 rows)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message psql-novice 2007-04-12 13:57:04 Setting schema from command line in psql
Previous Message A. Kretschmer 2007-04-12 05:07:34 Re: server variables ?