pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to get row number in select query
8+ messages / 4 participants
[nested] [flat]
* how to get row number in select query
@ 2011-01-26 16:11 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 8+ messages in thread
From: Emi Lu @ 2011-01-26 16:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-26 16:40 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Emi Lu <emilu@encs.concordia.ca>
1 sibling, 0 replies; 8+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-01-26 16:40 UTC (permalink / raw)
To: emilu@encs.concordia.ca; pgsql-sql
Hi, Lu Ying.
How do you define which row is #1 ? And #2 ?
E.g. Ordered by lname? Or gname...?
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-26 17:02 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 8+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-01-26 17:02 UTC (permalink / raw)
To: emilu@encs.concordia.ca; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-27 14:32 Emi Lu <emilu@encs.concordia.ca>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 8+ messages in thread
From: Emi Lu @ 2011-01-27 14:32 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
Hi Oliveiros,
> 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...
Thank you for the answer. I see psql8.4 has the method. I am not
interest in ordering any columns, but just a line num.
I'd like to get it from DB since displaytag _rowNum does not display row
num correctly: 1, 10, 11, 12.... 2, 3, 4, 5, 6...
Thanks,
--
Lu Ying
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-27 15:02 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-01-27 15:02 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
> Hi Oliveiros,
>
Howdy!
>> 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...
>
> Thank you for the answer. I see psql8.4 has the method. I am not interest
> in ordering any columns, but just a line num.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-27 15:21 Piotr Czekalski <pczekalski@techbaza.pl>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 8+ messages in thread
From: Piotr Czekalski @ 2011-01-27 15:21 UTC (permalink / raw)
To: pgsql-sql
Gentelmen,
I follow this thread and I don't exactly get an idea of yours, but isn't
is as simple as (example: table "web.files" contains one column named
"fileurl" ):
select row_number() over(), X.fileurl from (select fileurl from
web.files order by fileurl) X
The only disadvantage is that if you do want to order resultset you have
to use "select from select" as numbers are added before order which may
cause some performance troubles.
Regards,
Piotr
--
--------------------------------------------------------------
"TECHBAZA.PL" Sp. z o.o.
Technologie WEB, eDB& eCommerce
Oddział Gliwice
ul. Chorzowska 50
44-100 Gliwice
tel. (+4832) 7186081
fax. (+4832) 7003289
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-27 15:30 Thomas Kellerer <spam_eater@gmx.net>
parent: Piotr Czekalski <pczekalski@techbaza.pl>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2011-01-27 15:30 UTC (permalink / raw)
To: pgsql-sql
Piotr Czekalski, 27.01.2011 16:21:
> Gentelmen,
>
> I follow this thread and I don't exactly get an idea of yours, but
> isn't is as simple as (example: table "web.files" contains one column
> named "fileurl" ):
>
> select row_number() over(), X.fileurl from (select fileurl from
> web.files order by fileurl) X
>
> The only disadvantage is that if you do want to order resultset you
> have to use "select from select" as numbers are added before order
> which may cause some performance troubles.
>
You can get the row_number() without using the sub-select and without ordering the whole result as you can specify the order in the over() clause:
select fileurl
row_number() over (order by fileurl)
from web.files
Regards
Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: how to get row number in select query
@ 2011-01-31 16:39 Emi Lu <emilu@encs.concordia.ca>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Emi Lu @ 2011-01-31 16:39 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Ok, before 8.4, there I can use row_number().
For 8.3 + display tag + order by integer + paging based on pageBean
ArrayList<Bean> alist;
In Bean.java, added:
private int rec_num;
in main .action java:
for(int i=0 ; i<alist.size; i++)
{
Bean(i).setRec_num = (i+1);
...
}
This will let displaytag show rec num successfully.
--
Lu Ying
On 01/27/2011 10:30 AM, Thomas Kellerer wrote:
> Piotr Czekalski, 27.01.2011 16:21:
>> Gentelmen,
>>
>> I follow this thread and I don't exactly get an idea of yours, but
>> isn't is as simple as (example: table "web.files" contains one column
>> named "fileurl" ):
>>
>> select row_number() over(), X.fileurl from (select fileurl from
>> web.files order by fileurl) X
>>
>> The only disadvantage is that if you do want to order resultset you
>> have to use "select from select" as numbers are added before order
>> which may cause some performance troubles.
>>
>
> You can get the row_number() without using the sub-select and without
> ordering the whole result as you can specify the order in the over()
> clause:
>
> select fileurl
> row_number() over (order by fileurl)
> from web.files
>
> Regards
> Thomas
>
>
--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
emilu@encs.concordia.ca +1 514 848-2424 x5884
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-01-31 16:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-26 16:11 how to get row number in select query Emi Lu <emilu@encs.concordia.ca>
2011-01-26 16:40 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-01-26 17:02 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-01-27 14:32 ` Emi Lu <emilu@encs.concordia.ca>
2011-01-27 15:02 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-01-27 15:21 ` Piotr Czekalski <pczekalski@techbaza.pl>
2011-01-27 15:30 ` Thomas Kellerer <spam_eater@gmx.net>
2011-01-31 16:39 ` Emi Lu <emilu@encs.concordia.ca>
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