From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: emilu@encs.concordia.ca
To: pgsql-sql@postgresql.org
Subject: Re: how to get row number in select query
Date: Wed, 26 Jan 2011 17:02:33 -0000
Message-ID: <D25246ED495942DD8E5777F5F9439DD0@marktestcr.marktest.pt> (raw)
References: <4D4047C1.5030208@encs.concordia.ca>
If it is to order in ascendent fashion by, say, lname,
one possibility would be
SELECT COUNT(b.*) as row_number, a.lname,a.gname
FROM "Table1" a, "Table2" b
WHERE a.lname >= b.lname
GROUP BY a.lname,a.gname
ORDER BY row_number
If you want to order by gname just change the WHERE clause accordingly
N.B. : This works as long as there is no repetition on the column you use to
order.
If there is, we'll need a way to tie break. What is your specific case?
Also, note that this method is time consuming, and would work only for
relatively small tables.
AFAIK, version 8.3 doesn't have any "non-standard SQL" way to get a row
number, but it is possible that something like that has been introduced in
later versions...
Best,
Oliveiros
----- Original Message -----
From: "Emi Lu" <emilu@encs.concordia.ca>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, January 26, 2011 4:11 PM
Subject: [SQL] how to get row number in select query
> Good morning,
>
> For postgresql 8.3, what is the system method/key word to get row number
> please?
>
> E.g.,
>
> ==================
> lname1 gname1
> lname2 gname2
> lname3 gname3
> ......
>
> I'd like to get
>
> 1 lname1 gname1
> 2 lname2 gname2
> 3 lname3 gname3
>
> ... ...
>
> Something like
> select row_number?, lname, gname from Table1;
>
> Thanks a lot!
>
>
> --
> Lu Ying
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, emilu@encs.concordia.ca
Subject: Re: how to get row number in select query
In-Reply-To: <D25246ED495942DD8E5777F5F9439DD0@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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