Re: how to get row number in select query

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