Обсуждение: 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

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
> 
> 

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 

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

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...

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...

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...

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.