From ch@rodos.fzk.de Mon Sep 17 12:15:53 2001 Received: from resy5.fzk.de (resy5.fzk.de [141.52.68.8]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8HCFq865728 for ; Mon, 17 Sep 2001 08:15:53 -0400 (EDT) (envelope-from ch@rodos.fzk.de) Received: by rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA22585 for ; Mon, 17 Sep 2001 14:16:13 +0200 (METDST) From: Haller Christoph Message-Id: <200109171215.OAA21602@rodos> Subject: Re: Number the lines To: pgsql-sql@postgresql.org Date: Mon, 17 Sep 2001 14:15:32 METDST X-Mailer: Elm [revision: 212.4] X-Archive-Number: 200109/102 X-Sequence-Number: 4766 Am I right in assuming you were asking for something like SELECT Rownumbering(), ... FROM ORDER BY ; 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 From yaubi@yaubi.com Fri Sep 14 12:19:44 2001 Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8ECJi732013 for ; Fri, 14 Sep 2001 08:19:44 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8EC5qn45535 for pgsql-sql@postgresql.org; Fri, 14 Sep 2001 08:05:52 -0400 (EDT) (envelope-from news) From: yaubi@yaubi.com (Yoann) X-Newsgroups: comp.databases.postgresql.sql Subject: Number the lines Date: 14 Sep 2001 05:05:49 -0700 Organization: http://groups.google.com/ Lines: 8 Message-ID: <70c8063b.0109140405.1895bf0d@posting.google.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200109/78 X-Sequence-Number: 4724 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 From jeckermann@verio.net Fri Sep 14 18:50:38 2001 Received: from akirason.se.verio.net (akirason.se.verio.net [204.27.64.122]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EIoc756374 for ; Fri, 14 Sep 2001 14:50:38 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: from beancounter (no-wkstns.gs.verio.net [204.27.67.225]) (authenticated) by akirason.se.verio.net (8.11.4/8.11.4) with ESMTP id f8EIs8I15751; Fri, 14 Sep 2001 13:54:10 -0500 (CDT) (envelope-from jeckermann@verio.net) Message-ID: <020301c13d4e$1d1d7de0$279c10ac@INTERNAL> From: "Jeff Eckermann" To: "Yoann" , References: <70c8063b.0109140405.1895bf0d@posting.google.com> Subject: Re: Number the lines Date: Fri, 14 Sep 2001 13:50:15 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200109/87 X-Sequence-Number: 4747 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" To: 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 > > From missive@frontiernet.net Sat Sep 15 03:19:47 2001 Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8F3Jl738823 for ; Fri, 14 Sep 2001 23:19:47 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8F3CQf65939 for pgsql-sql@postgresql.org; Fri, 14 Sep 2001 23:12:26 -0400 (EDT) (envelope-from news) From: missive@frontiernet.net (Lee Harr) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Number the lines Date: Sat, 15 Sep 2001 03:12:26 +0000 (UTC) Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 30 Message-ID: <9nugup$20cg$1@news.tht.net> References: <70c8063b.0109140405.1895bf0d@posting.google.com> Reply-To: missive@hotmail.com X-Complaints-To: scrappy@hub.org User-Agent: slrn/0.9.7.0 (FreeBSD) To: pgsql-sql@postgresql.org X-Archive-Number: 200109/110 X-Sequence-Number: 4775 On 14 Sep 2001 05:05:49 -0700, Yoann 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... From kb136@hszk.bme.hu Mon Sep 17 12:54:55 2001 Received: from ural2.hszk.bme.hu (ural2.hszk.bme.hu [152.66.130.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8HCst883884 for ; Mon, 17 Sep 2001 08:54:55 -0400 (EDT) (envelope-from kb136@hszk.bme.hu) Received: (from kb136@localhost) by ural2.hszk.bme.hu (8.11.3/8.11.3) id f8HCspZ29241; Mon, 17 Sep 2001 14:54:51 +0200 (MEST) Date: Mon, 17 Sep 2001 14:54:51 +0200 (MEST) From: Kovacs Baldvin X-X-Sender: To: Haller Christoph cc: Subject: Re: Number the lines In-Reply-To: <200109171215.OAA21602@rodos> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200109/105 X-Sequence-Number: 4769 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 From csawtell@paradise.net.nz Tue Sep 18 22:54:58 2001 Received: from deborah.paradise.net.nz (deborah.paradise.net.nz [203.96.152.32]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8IMsvd77811 for ; Tue, 18 Sep 2001 18:54:58 -0400 (EDT) (envelope-from csawtell@paradise.net.nz) Received: from berty.localnet (202-0-37-152.cable.paradise.net.nz [202.0.37.152]) by deborah.paradise.net.nz (Postfix) with ESMTP id C87FF1F9EEB; Wed, 19 Sep 2001 10:54:49 +1200 (NZST) Subject: Re: Number the lines From: Christopher Sawtell To: Kovacs Baldvin Cc: pgsql-sql@postgresql.org In-Reply-To: Content-Type: text/plain X-Mailer: Evolution (0.9 - Preview Release) Date: 19 Sep 2001 10:54:26 +1200 Mime-Version: 1.0 Message-Id: <20010918225449.C87FF1F9EEB@deborah.paradise.net.nz> X-Archive-Number: 200109/131 X-Sequence-Number: 4809 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.