Re: Probably simple answer

On Thu, 01 Nov 2001 14:24:29 -0600
"Al Kirkus" wrote:

> Can anyone tell me how to get a sequential row count field in the output of a query?
>
> Say I want to query for all users in a table sorted by lastname and firstname.
> I would like to include a column in my query called "rownum" which would uniquely
> identify the row in the order of the query results.
>
> Like this:
>
> rownum =1 lastname=jones, firstname=john
> rownum=2 lastname=smith, firstname=john
>
> etc.
> I assume rownum should be some kind of function of expresion but I don't know what.
>
> Something like:
>
> Select ???? as rownum, lastname,firstname from users
> where xxx =xxx order by lastname, firsname.
>

Ugh, that sounds like an oracle command. Instead of a rownum,
as I understand it, you need to use a sequence which has already
mentioned by Joshua. A following query A or B is what you want
to select, isn't it?

drop sequence seq_test_tbl;
create sequence seq_test_tbl;

drop table test_tbl;
create table test_tbl (firstname varchar(20) not null,
lastname varchar(20) not null);
insert into test_tbl values('john', 'jones');
insert into test_tbl values('john', 'smith');
insert into test_tbl values('shiri', 'appleby');
insert into test_tbl values('jason', 'behr');

-- query A
select setval('seq_test_tbl',1);
select (nextval('seq_test_tbl')-1) as rownum, t1.lastname, t1.firstname
from (select t0.lastname, t0.firstname
from test_tbl as t0
where firstname like 'j%'
order by t0.lastname, t0.firstname
) as t1
;

-- query B
select (nextval('seq_test_tbl')-1) as rownum, t1.lastname, t1.firstname
from (select t0.lastname, t0.firstname
from test_tbl as t0,
(select setval('seq_test_tbl',1)) as dummy
where firstname like 'j%'
order by t0.lastname, t0.firstname
) as t1
;

rownum | lastname | firstname
--------+----------+-----------
1 | behr | jason
2 | jones | john
3 | smith | john
(3 rows)

Regards,
Masaru Sugawara