Thread: Number the lines

Number the lines

From
yaubi@yaubi.com (Yoann)
Date:
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


Re: Number the lines

From
"Jeff Eckermann"
Date:
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
> 
> 



Re: Number the lines

From
Haller Christoph
Date:
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 


Re: Number the lines

From
Kovacs Baldvin
Date:
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




Re: Number the lines

From
missive@frontiernet.net (Lee Harr)
Date:
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...



Re: Number the lines

From
missive@frontiernet.net (Lee Harr)
Date:
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...



Re: Number the lines

From
missive@frontiernet.net (Lee Harr)
Date:
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...



Re: Number the lines

From
Christopher Sawtell
Date:
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.