Re: How to do?

В Сбт, 02.08.2003, в 01:55, Ron Johnson пишет:
> On Fri, 2003-08-01 at 11:44, Robert Partyka wrote:
> > Ron Johnson wrote:
> >  > No, but slightly ambiguous, at least for my old brain.
> > I will try to by more unequivocal this time :)
> >
> > Shridhar Daithankar wrote:
> >  > select oid,name from a;
> > I know it, but i have to have not oid's but row numbers :) such like :
> >      table "test"
> >     offset  |   value
> > -----------+------------
> > 1          |    AC43
> > 2          |    AC4X
> > 3          |    AX43
> > 4          |    ACX3
> > ....
> > n          |    XC4A
> >
> > the best will be without using sequence :)
>
> But relational algebra and SQL don't have the concept of row numbers.
> "row numbers" like OIDs are internal constructs that are exposed to
> the users, but shouldn't be used.

There's a trick if you have a unique sortable column in the table, it
goes like this:

create table a (id serial primary key, data text);
insert into a values (default, 'fds');
insert into a values (default, 'fdsas');
insert into a values (default, 'sas');
insert into a values (default, 'asf');
select * from a;
 id | data
----+-------
  1 | fds
  2 | fdsas
  3 | sas
  4 | asf
(записей: 4)
(select count(1) from a where a.data <= b.data) as rownumber, * from a b order by data;
 rownumber | id | data
-----------+----+-------
         1 |  4 | asf
         2 |  1 | fds
         3 |  2 | fdsas
         4 |  3 | sas
(записей: 4)

--
Markus Bertheau <twanger@bluetwanger.de>