pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Number the lines
6+ messages / 6 participants
[nested] [flat]

* Re: Number the lines
@ 1970-01-01 00:00  Haller Christoph <ch@rodos.fzk.de>
  0 siblings, 1 reply; 6+ messages in thread

From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
  To: pgsql-sql

Am I right in assuming you were asking for 
something like 
SELECT Rownumbering(), ... FROM <Table> ORDER BY <Column> ; 
and expecting a result like 
 1| ... 
 2| ...
 3| ...
... 
where Rownumbering() is a PostgreSQL function 

If this is what you wanted, this is something 
I want for a long time too. 
Maybe we should stick together and tell the 
PostgreSQL hackers about our wish. 
Or maybe something like this already exists, 
but we haven't noticed yet. 
Regards, Christoph 



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

* Number the lines
@ 2001-09-14 12:05  Yoann <yaubi@yaubi.com>
  0 siblings, 2 replies; 6+ messages in thread

From: yaubi@yaubi.com @ 2001-09-14 12:05 UTC (permalink / raw)
  To: pgsql-sql

how can I number the result's lines of a sql query ?

explaination : I have a query which return me a list of values. I need
to order them (it's ok, easy ;) and then number the lines. The goal is
then to extract, for example, "the third maximum value".

Tx in advance !
Yoann



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

* Re: Number the lines
@ 2001-09-14 18:50  Jeff Eckermann <jeckermann@verio.net>
  parent: Yoann <yaubi@yaubi.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Jeff Eckermann @ 2001-09-14 18:50 UTC (permalink / raw)
  To: Yoann <yaubi@yaubi.com>; pgsql-sql

If you want "the third maximum value", easist to do:
SELECT * FROM table ORDER BY whatever DESC OFFSET 2 LIMIT 1;

----- Original Message ----- 
From: "Yoann" <yaubi@yaubi.com>
To: <pgsql-sql@postgresql.org>
Sent: Friday, September 14, 2001 7:05 AM
Subject: Number the lines


> how can I number the result's lines of a sql query ?
> 
> explaination : I have a query which return me a list of values. I need
> to order them (it's ok, easy ;) and then number the lines. The goal is
> then to extract, for example, "the third maximum value".
> 
> Tx in advance !
> Yoann
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
> 




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

* Re: Number the lines
@ 2001-09-15 03:12  Lee Harr <missive@frontiernet.net>
  parent: Yoann <yaubi@yaubi.com>
  1 sibling, 0 replies; 6+ messages in thread

From: missive@frontiernet.net @ 2001-09-15 03:12 UTC (permalink / raw)
  To: pgsql-sql

On 14 Sep 2001 05:05:49 -0700, Yoann <yaubi@yaubi.com> wrote:
> how can I number the result's lines of a sql query ?
> 
> explaination : I have a query which return me a list of values. I need
> to order them (it's ok, easy ;) and then number the lines. The goal is
> then to extract, for example, "the third maximum value".
> 

CREATE TABLE value (i integer);

INSERT INTO i VALUES (1);
INSERT INTO i VALUES (3);
INSERT INTO i VALUES (2);
INSERT INTO i VALUES (1);
INSERT INTO i VALUES (5);
INSERT INTO i VALUES (2);

CREATE VIEW ordered AS SELECT i FROM value ORDER BY i DESC;

BEGIN;

CREATE SEQUENCE seq;

SELECT nextval('seq') as n, i INTO TEMP TABLE t FROM ordered;

SELECT i FROM t WHERE n=3;


-- IANADBA, but something like this might work...




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

* Re: Number the lines
@ 2001-09-17 12:54  Kovacs Baldvin <kb136@hszk.bme.hu>
  parent: Haller Christoph <ch@rodos.fzk.de>
  0 siblings, 1 reply; 6+ messages in thread

From: Kovacs Baldvin @ 2001-09-17 12:54 UTC (permalink / raw)
  To: Haller Christoph <ch@rodos.fzk.de>; +Cc: pgsql-sql

Hi!

I am in the university now, not be able to test what I
say, so please forgive me if it is buggy or wrong.

So does it helps you?:

CREATE SEQUENCE ordering;
SELECT NEXTVAL('ordering') as rownumber, *
  INTO TABLE anewname FROM whatyouwant;

Probably CREATE SEQUENCE 'ordering';, do try it...

Bye,
Baldvin





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

* Re: Number the lines
@ 2001-09-18 22:54  Christopher Sawtell <csawtell@paradise.net.nz>
  parent: Kovacs Baldvin <kb136@hszk.bme.hu>
  0 siblings, 0 replies; 6+ messages in thread

From: Christopher Sawtell @ 2001-09-18 22:54 UTC (permalink / raw)
  To: Kovacs Baldvin <kb136@hszk.bme.hu>; +Cc: pgsql-sql

On 17 Sep 2001 14:54:51 +0200, Kovacs Baldvin wrote:
> Hi!
> 
> I am in the university now, not be able to test what I
> say, so please forgive me if it is buggy or wrong.
> 
> So does it helps you?:


That works correctly. Thanks very much for the tip
.
Do not forget to DROP the sequence afterwards, otherwise you will
find the line numbers continuing the sequence in the next SELECT
statement.

> CREATE SEQUENCE ordering;
> SELECT NEXTVAL('ordering') as rownumber, *
>   INTO TABLE anewname FROM whatyouwant;
> 
> Probably CREATE SEQUENCE 'ordering';, do try it...


No, you do not need the quote characters. 

Sincerely etc.. C.





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


end of thread, other threads:[~2001-09-18 22:54 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Re: Number the lines Haller Christoph <ch@rodos.fzk.de>
2001-09-17 12:54 ` Kovacs Baldvin <kb136@hszk.bme.hu>
2001-09-18 22:54   ` Christopher Sawtell <csawtell@paradise.net.nz>
2001-09-14 12:05 Number the lines Yoann <yaubi@yaubi.com>
2001-09-14 18:50 ` Jeff Eckermann <jeckermann@verio.net>
2001-09-15 03:12 ` Lee Harr <missive@frontiernet.net>

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