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