pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Line Numbering in SELRCT Output
5+ messages / 3 participants
[nested] [flat]

* Line Numbering in SELRCT Output
@ 2002-03-09 23:48  Hubert Palme <hubert.palme@web.de>
  0 siblings, 1 reply; 5+ messages in thread

From: Hubert Palme @ 2002-03-09 23:48 UTC (permalink / raw)
  To: pgsql-sql

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>



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

* Re: Line Numbering in SELRCT Output
@ 2002-03-10 15:33  Paul <magamos@mail.ru>
  parent: Hubert Palme <hubert.palme@web.de>
  0 siblings, 1 reply; 5+ messages in thread

From: Paul @ 2002-03-10 15:33 UTC (permalink / raw)
  To: pgsql-sql

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>





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

* Re: Line Numbering in SELRCT Output
@ 2002-03-11 22:55  Hubert Palme <hubert.palme@web.de>
  parent: Paul <magamos@mail.ru>
  0 siblings, 1 reply; 5+ messages in thread

From: Hubert Palme @ 2002-03-11 22:55 UTC (permalink / raw)
  To: pgsql-sql

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>



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

* Re: Line Numbering in SELRCT Output
@ 2002-03-14 01:01  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Hubert Palme <hubert.palme@web.de>
  0 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2002-03-14 01:01 UTC (permalink / raw)
  To: Hubert Palme <hubert.palme@web.de>; +Cc: pgsql-sql

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;




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

* Re: Line Numbering in SELRCT Output
@ 2002-03-18 22:51  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2002-03-18 22:51 UTC (permalink / raw)
  To: Hubert Palme <hubert.palme@web.de>; +Cc: pgsql-sql

On Mon, 18 Mar 2002, Hubert Palme wrote:

> >>>>> "S" == Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>
>  S> 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?
>
>  S> select nextval('seq1'), foo.* from (select ...) foo;
>
> I tried this:
>
>CREATE SEQUENCE z_nummer;
>SELECT nextval('z_nummer'), hilfstab.name, hilfstab.vorname
>FROM (SELECT *
>     FROM leute
>      ORDER BY name, vorname) hilfstab;
> DROP SEQUENCE z_nummer;
>
> and got this result:
>
> mitglieder=> \i selorder.sql
> CREATE
> psql:selorder.sql:5: ERROR:  parser: parse error at or near "select"
> DROP
> mitglieder=>
>
> Did I understand you right, this kind of subqueries in FROM clauses
> are allowed?  (Didn't find it in any manual)

What version are you using?
AFAIK that structure is allowed in 7.1 and after.




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


end of thread, other threads:[~2002-03-18 22:51 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-09 23:48 Line Numbering in SELRCT Output Hubert Palme <hubert.palme@web.de>
2002-03-10 15:33 ` Paul <magamos@mail.ru>
2002-03-11 22:55   ` Hubert Palme <hubert.palme@web.de>
2002-03-14 01:01     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-18 22:51 Re: Line Numbering in SELRCT Output Stephan Szabo <sszabo@megazone23.bigpanda.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