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(at)bluetwanger(dot)de>