Probably simple answer

Hello,

You could either select the oid along with your query, or alternatively
set up a sequence:

http://www.postgresql.info/r23450.htm

Sincerely,

Joshua Drake
Co-Author: Practical PostgreSQL

On Thu, 1 Nov 2001, Al Kirkus wrote:

Hi all.

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
--
by way of pgsql-general@commandprompt.com
http://www.postgresql.info/
http://www.commandprompt.com/

I think you need to define more clearly what you want as an end
product. You could create a serial column however there are
possibilities of skipped numbers. Next, how do these 'row number'
relate to your data when you delete a row? Do you expect that the
remaining rows will re-number themselves? Is this 'number' a client
id number?

This sounds like you need to better define your data needs.
Before anyone can help, you need to be clearer.

Ted

-----Original Message-----
From: "Al Kirkus" <Al@dist102.k12.il.us>
To: pgsql-general@postgresql.org
Date: Thu, 01 Nov 2001 14:24:29 -0600
Subject: [GENERAL] Probably simple answer

Hi all.

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.

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an

appropriate

subscribe-nomail command to majordomo@postgresql.org so

that your

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

I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is. This is
useful for referencing rows when discussing reports.
It is also an immense help when calculating a record's
statistical percentile ranking.

Doing it in a table is no problem. But how do you
create one dynamically in a select query?

I too would like to know if and how this could be
done.

Any ideas?

Andrew Gould

--- postgresql <pgsql@symcom.com> wrote:

I think you need to define more clearly what you
want as an end
product. You could create a serial column however
there are
possibilities of skipped numbers. Next, how do these
'row number'
relate to your data when you delete a row? Do you
expect that the
remaining rows will re-number themselves? Is this
'number' a client
id number?

This sounds like you need to better define your data
needs.
Before anyone can help, you need to be clearer.

Ted

-----Original Message-----
From: "Al Kirkus" <Al@dist102.k12.il.us>
To: pgsql-general@postgresql.org
Date: Thu, 01 Nov 2001 14:24:29 -0600
Subject: [GENERAL] Probably simple answer

Hi all.

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.

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please

send an
appropriate

subscribe-nomail command to

majordomo@postgresql.org so
that your

message can get through to the mailing list

cleanly

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

You could use a procedure to select the data into a table which has a sequence as a default for the number column, then select your data out of that. The function could return a key if the same table might be used by more than one user, in which case the sequence would have to be set back to value '1' for each use, or you could create a temp table each time.

It seems like psql could be hacked to do something like this. Alternatively, whichever client you are using could also be hacked. It seems simpler to add the numbering as the data is presented to the user than to do it in the database, especially since the numbers have no shelf life. They are meaningless after any of the underlying data has been deleted or appended.

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

Andrew Gould <andrewgould@yahoo.com> 11/06/01 07:41AM >>>

I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is. This is
useful for referencing rows when discussing reports.
It is also an immense help when calculating a record's
statistical percentile ranking.

Doing it in a table is no problem. But how do you
create one dynamically in a select query?

I too would like to know if and how this could be
done.

Any ideas?

Andrew Gould

--- postgresql <pgsql@symcom.com> wrote:

I think you need to define more clearly what you
want as an end
product. You could create a serial column however
there are
possibilities of skipped numbers. Next, how do these
'row number'
relate to your data when you delete a row? Do you
expect that the
remaining rows will re-number themselves? Is this
'number' a client
id number?

This sounds like you need to better define your data
needs.
Before anyone can help, you need to be clearer.

Ted

-----Original Message-----
From: "Al Kirkus" <Al@dist102.k12.il.us>
To: pgsql-general@postgresql.org
Date: Thu, 01 Nov 2001 14:24:29 -0600
Subject: [GENERAL] Probably simple answer

Hi all.

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.

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please

send an
appropriate

subscribe-nomail command to

majordomo@postgresql.org so
that your

message can get through to the mailing list

cleanly

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Tue, Nov 06, 2001 at 07:41:06AM -0800, Andrew Gould wrote:

I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is. This is
useful for referencing rows when discussing reports.
It is also an immense help when calculating a record's
statistical percentile ranking.

Doing it in a table is no problem. But how do you
create one dynamically in a select query?

What about using a sequence inside a transaction:

scratch=# \d tmp1
Table "tmp1"
Attribute | Type | Modifier
-----------+------+----------
col1 | text |

scratch=# begin ;
BEGIN
scratch=# create SEQUENCE temp_seq minvalue 1 increment 1;
CREATE
scratch=# select nextval('temp_seq') as rownum, col1 from tmp1 ;
rownum | col1
--------+------
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
(6 rows)

scratch=# rollback;
ROLLBACK

The ROLLBACK gets rid of the sequence, so you don't have it hanging
around, and since you're in a transaction, no-one else can see your
sequence, so it won't get incremented by someone else calling to it.
Not perfect, but for on-the-fly row numbering, it might work.

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

On Wed, 07 Nov 2001 00:40:50 +0900
I wrote:

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?

Note !
In case of multi access, you must create sequences with unique name
per user or client PC, and must make each user or each PC use an
allocated sequence, respectively. In addition, each user or each PC
mustn't execute more than two queries at one time.

Probably, It doesn't increase evenly if there happen to be multi access
to a sequence.

Masaru Sugawara

Al Kirkus writes:

Can anyone tell me how to get a sequential row count field in the output of a query?

Just keep a counter when you process the rows in your client application.
Depending on the particular interface you'll have to do that anyway to
loop through the result set.

--
Peter Eisentraut peter_e@gmx.net

Thanks.
That just might work!
Al

Andrew Sullivan <andrew@libertyrms.info> - 11/6/01 12:12 PM >>>

On Tue, Nov 06, 2001 at 07:41:06AM -0800, Andrew Gould wrote:

I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is. This is
useful for referencing rows when discussing reports.
It is also an immense help when calculating a record's
statistical percentile ranking.

Doing it in a table is no problem. But how do you
create one dynamically in a select query?

What about using a sequence inside a transaction:

scratch=# \d tmp1
Table "tmp1"
Attribute | Type | Modifier
-----------+------+----------
col1 | text |

scratch=# begin ;
BEGIN
scratch=# create SEQUENCE temp_seq minvalue 1 increment 1;
CREATE
scratch=# select nextval('temp_seq') as rownum, col1 from tmp1 ;
rownum | col1
--------+------
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
(6 rows)

scratch=# rollback;
ROLLBACK

The ROLLBACK gets rid of the sequence, so you don't have it hanging
around, and since you're in a transaction, no-one else can see your
sequence, so it won't get incremented by someone else calling to it.
Not perfect, but for on-the-fly row numbering, it might work.

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Andrew Gould writes:

I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is. This is
useful for referencing rows when discussing reports.
It is also an immense help when calculating a record's
statistical percentile ranking.

Doing it in a table is no problem. But how do you
create one dynamically in a select query?

It's not easily possible.

Even if you were to write a server-side function that would implement such
a counter, there is no guarantee that the rows would be returned in the
order that the function is called in. (Depending on how you express the
query it can be very likely, but there is no guarantee.)

It gets worse if you use ORDER BY or DISTINCT. Then the problem amounts
to adding a column to the result set after sorting, which is not possible
in the SQL query model.

So by far the easiest choice is to keep this counter in the client
application as you loop through the result set.

--
Peter Eisentraut peter_e@gmx.net

Thanks. Sometimes there's just no getting around a
little work. :-)

Andrew Gould

--- Peter Eisentraut <peter_e@gmx.net> wrote:

Andrew Gould writes:

I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is. This

is

useful for referencing rows when discussing

reports.

It is also an immense help when calculating a

record's

statistical percentile ranking.

Doing it in a table is no problem. But how do you
create one dynamically in a select query?

It's not easily possible.

Even if you were to write a server-side function
that would implement such
a counter, there is no guarantee that the rows would
be returned in the
order that the function is called in. (Depending on
how you express the
query it can be very likely, but there is no
guarantee.)

It gets worse if you use ORDER BY or DISTINCT. Then
the problem amounts
to adding a column to the result set after sorting,
which is not possible
in the SQL query model.

So by far the easiest choice is to keep this counter
in the client
application as you loop through the result set.

--
Peter Eisentraut peter_e@gmx.net

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to

majordomo@postgresql.org

__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com