Thread: 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 queryresults. 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.
I can't think of a simple answer. I think you would need some kind of programmatic solution like a client side program thatwould create a temporary table with a serial field in it, populate it with data based on your query, then return thedata. The temp table would go away with the connection. A function could create the table but you would still have toquery the resulting table since functions can't return query results directly yet. Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: ianh@tpchd.org >>> "Al Kirkus" <Al@dist102.k12.il.us> 11/01/01 12:24PM >>> 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 queryresults. 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
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 thequery 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 > message can get through to the mailing list cleanly >
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
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
You could use a procedure to select the data into a table which has a sequence as a default for the number column, then selectyour data out of that. The function could return a key if the same table might be used by more than one user, in whichcase 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 behacked. It seems simpler to add the numbering as the data is presented to the user than to do it in the database, especiallysince the numbers have no shelf life. They are meaningless after any of the underlying data has been deleted orappended. 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