Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group