Re: Probably simple answer

Поиск
Список
Период
Сортировка
От Masaru Sugawara
Тема Re: Probably simple answer
Дата
Msg-id 20011107003656.D194.RK73@echna.ne.jp
обсуждение исходный текст
Ответ на Probably simple answer  ("Al Kirkus" <Al@dist102.k12.il.us>)
Ответы Re: Probably simple answer  (Masaru Sugawara <rk73@echna.ne.jp>)
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: data integrity
Следующее
От: "Johnson, Shaunn"
Дата:
Сообщение: Re: Equate for "describe table" ?