Thread: Re: Line Numbering in SELRCT Output

CREATE SEQUENCE seq1;

SELECT nextval('seq1'), amname FROM pg_am;

DROP SEQUENCE seq1;

"Hubert Palme" <hubert.palme@web.de> wrote in message
news:a6e70l$2o5p$1@jupiter.hub.org...
> Hi,
>
> how can I get line numbers in the output of a select statement, e.g.
>
> No.  | Name             | ...
> -----+------------------+-------
>    1 | Meyer            | ...
>    2 | Miller           | ...
>    3 | Mueller          | ...
>    4 | Smith            | ...
>
> Thanks in advance,
> --
> Hubert Palme                           Am Walde 1
>                                  42119 Wuppertal
> <hubert.palme@web.de>

Paul wrote:

> CREATE SEQUENCE seq1;
> 
> SELECT nextval('seq1'), amname FROM pg_am;
> 
> DROP SEQUENCE seq1;
> 

Hmm... I forgot to mention that my SELECT statement has an ORDER BY clause. 
The sort is done after the sequence numbers being generated. So the order 
of the sequence numbers becomes damaged by the sort.

Is there any hint how to avoid this?

-- 
Hubert Palme                           Am Walde 1                                42119 Wuppertal
<hubert.palme@web.de>

On Mon, 11 Mar 2002, Hubert Palme wrote:

> Paul wrote:
>
> > CREATE SEQUENCE seq1;
> >
> > SELECT nextval('seq1'), amname FROM pg_am;
> >
> > DROP SEQUENCE seq1;
> >
>
> Hmm... I forgot to mention that my SELECT statement has an ORDER BY clause.
> The sort is done after the sequence numbers being generated. So the order
> of the sequence numbers becomes damaged by the sort.
>
> Is there any hint how to avoid this?

select nextval('seq1'), foo.* from (select ...) foo;