Обсуждение: 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
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
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
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
> 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. > From your reply, I don't know if my answer did solve your problem. Did it? My idea was to supply you with a pure SQL solution, but the way it works, it does need some ordering criterion... > 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... > What do you mean exactly with "get it from DB" ? To Include it in an additional column on your table? > Thanks, > -- > Lu Ying > Best, Oliveiros > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/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
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 orderin the over() clause: select fileurl row_number() over (order by fileurl) from web.files Regards Thomas
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