Re: how to get row number in select query

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql