row numbering

josue wrote:

is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.

No, but you can easily keep a counter in the client.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

If you insert the results of your query into a table with a serial
column, the serial column will do what you want..

On Sat, 2005-02-26 at 01:10 +0100, Peter Eisentraut wrote:

josue wrote:

is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.

No, but you can easily keep a counter in the client.

--
Mike Harding <mvh@ix.netcom.com>

josue <josue@lamundial.hn> writes:

to something like:

select counter(),a,b from foo;

The OLAP SQL Standard way to spell this is "ROW_NUMBER() OVER ()".

Postgres doesn't have any support for any of the OLAP features however. It
would be really nice because they're nigh impossible to emulate with standard
SQL.

You might be able to get away with a sequence, but if you have multiple
connections running this query at the same time then that will be awkward. You
would have to create a new sequence for the query every time.

If you can you're probably best off doing this in the client. You can do any
sort of level break logic you need in the client, and even refer to previous
records. The only thing that would make doing it in the client awkward would
be if you were planning to use the results in more query logic such as a join.

--
greg

Here's an example using plperl and global variables. The variables are
local to a session so you don't have to worry about the counters
interfering. If you need two counters in a session, just execute
reset_counter().

CREATE OR REPLACE FUNCTION reset_counter() RETURNS INT AS $$
$_SHARED{counter} = 0;
return 0;
$$ LANGAUGE plperl;

CREATE OR REPLACE FUNCTION counter() RETURNS INT AS $$
return $_SHARED{counter}++;
$$ LANGUAGE plperl;

Now, you can execute the queries just like you want:
select counter(),a,b from foo;

There are a couple trivial issues, like you can start from 1 instead of
0 if you want.

Regards,
Jeff Davis

Jeff Davis wrote:

Here's an example using plperl and global variables. The variables are
local to a session so you don't have to worry about the counters
interfering. If you need two counters in a session, just execute
reset_counter().

CREATE OR REPLACE FUNCTION reset_counter() RETURNS INT AS $$
$_SHARED{counter} = 0;
return 0;
$$ LANGAUGE plperl;

CREATE OR REPLACE FUNCTION counter() RETURNS INT AS $$
return $_SHARED{counter}++;
$$ LANGUAGE plperl;

Now, you can execute the queries just like you want:
select counter(),a,b from foo;

There are a couple trivial issues, like you can start from 1 instead of
0 if you want.

Thanks, all the ideas you and the other members gave me have been very
helpfully

--
Sinceramente,
Josu� Maldonado.

... "Ser fiel supone enga�ar a tu pareja en el momento justo"

On Fri, 2005-02-25 at 16:44, josue wrote:

Hello list,

is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.

For instance:
select a,b from foo;
a b
20 yes
40 no
15 yes

to something like:

select counter(),a,b from foo;
counter a b
1 20 yes
2 40 no
3 15 yes

You could use a temporary sequence:

create temporary sequence counter;
select nextval('counter'),* from sometable;

is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.

No, but you can easily keep a counter in the client.

How, then, do I do it if I "need" the "row number" in a view ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

OT: You have other database issues: http://www.gnumed.org/

<snip>
error
insert into WebLog values(586,31,"2005-02-28","<ip addr removed>")
Duplicate entry '2005-02-28' for key 2
</snip>

On Sun, 27 Feb 2005 18:08:02 +0100, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:

Karsten Hilbert wrote:

is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.

No, but you can easily keep a counter in the client.

How, then, do I do it if I "need" the "row number" in a view ?

Keep the counter in the client as you process or display the result
rows.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

If you insert the results of your query into a table with a serial
column, the serial column will do what you want..

Plus add a huge overload... ?

Having some sort of line numbering in result query would be nice...

----- Original Message -----
From: "Mike Harding" <mvh@ix.netcom.com>
To: "Peter Eisentraut" <peter_e@gmx.net>
Cc: "josue" <josue@lamundial.hn>; <pgsql-general@postgresql.org>
Sent: Saturday, February 26, 2005 1:27 AM
Subject: Re: [GENERAL] row numbering

NTPT wrote:

Having some sort of line numbering in result query would be nice...

The query result has line numbering. How else are you accessing the
individual rows?

Is the issue really that you want psql to number the lines on display?
That could be implemented.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

is there a way return a column with the row number automatically
generated according the way the rows were processed by the query.

No, but you can easily keep a counter in the client.

How, then, do I do it if I "need" the "row number" in a view ?

Keep the counter in the client as you process or display the result
rows.

Well, but I'd like to use those "row numbers" in a comparison
right inside the view definition ...

Eg.:

There are 5 vaccinations in a given vaccination schedule.

Patient had 3 shots.

I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

OT: You have other database issues: http://www.gnumed.org/

<snip>
error
insert into WebLog values(586,31,"2005-02-28","<ip addr removed>")
Duplicate entry '2005-02-28' for key 2
</snip>

Yes I do and no I don't. That database underlies a "Wiki"
written by one of our contributors. Nothing directly related
to GnuMed or it's schema itself.

Thanks for pointing this out.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert wrote:

There are 5 vaccinations in a given vaccination schedule.

Patient had 3 shots.

I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.

That sounds like you are trying to abuse the data model, so I'm not
surprised that it isn't easily possible. As the data stored in a table
is inherently unordered, you can't really talk about order unless you
impose it yourself by way of assigning ordinal numbers or some other
sort key to your rows.

Even if you could, say, assign a fixed order to tables or views or
actually had some kind of automatic row number available, that would
still make the semantics of your data dependent of the particularities
of the queries that you use to access it, which doesn't sound like a
good idea to me.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, Feb 28, 2005 at 17:46:43 +0100,
Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

There are 5 vaccinations in a given vaccination schedule.

Patient had 3 shots.

I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.

For this kind of task you usually want to use a left (or right) join.

There are 5 vaccinations in a given vaccination schedule.

Patient had 3 shots.

I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.

That sounds like you are trying to abuse the data model, so I'm not
surprised that it isn't easily possible. As the data stored in a table
is inherently unordered,

I know. I don't expect rows in tables to be ordered in any
way.

you can't really talk about order unless you
impose it yourself by way of assigning ordinal numbers or some other
sort key to your rows.

Here is the bit of data that I forgot to mention: Those
consecutive immunization rows *are* ordered by a sort key that
the application assigns -- the date of application. So,
basically, what I want to do is the following (high level):

1) read given vaccinations from table
2) order by date_given
3) assign ordinals to the rows in the order obtained by 2)
4) deduce missing shots by joining to another table that
defines the number of vaccinations in a schedule

Sure, I can do this in client code just fine. I would prefer
to put that into a view, however. Having "output row numbers"
doesn't make that *easy* but it seems it's the missing link to
making it *possible* in SQL. I am not asking for "row numbers"
for tables - which is nonsense - but rather for optional
numbering of query result rows.

Even if you could, say, assign a fixed order to tables or views or
actually had some kind of automatic row number available, that would
still make the semantics of your data dependent of the particularities
of the queries that you use to access it, which doesn't sound like a
good idea to me.

Understood. That wasn't what I was after. I poorly presented
the case.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

There are 5 vaccinations in a given vaccination schedule.

Patient had 3 shots.

I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.

For this kind of task you usually want to use a left (or right) join.

I thought so. I tried to get it done that way several
times. I asked on this list more than once, too.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

I figured it out, maybe is not the most elegant way but it work for my
case where only small sets are retrieved

create table foo2 (pk int, valor numeric(12,2), porce numeric(5,2));

insert into foo2 values (1,7893.45,0.4);
insert into foo2 values (5,7893.45,0.3);
insert into foo2 values (9,7893.45,0.3);

select *,
(select count(*) from foo2 as f2
where f2.oid <= foo2.oid) as counter
from foo2;

--
Sinceramente,
Josu� Maldonado.

... "Un cient�fico es un hombre tan endeble y humano como cualquiera;
sin embargo, la b�squeda cient�fica puede ennoblecerle, incluso en
contra de su voluntad." -- Isaac Asimov

# Karsten.Hilbert@gmx.net / 2005-03-01 17:41:46 +0100:

There are 5 vaccinations in a given vaccination schedule.

Patient had 3 shots.

I want the view to show me that shot 4 and 5 are missing
without having to enter the cardinality of the vaccination in
the original data.

I don't know that much about medicine, so this might be a funny
question, but do you really need to know that "shots 4 and 5 are
missing", or just that the patient needs to be shot two more times,
or do you really want the *application dates*?

Here is the bit of data that I forgot to mention: Those
consecutive immunization rows *are* ordered by a sort key that
the application assigns -- the date of application. So,
basically, what I want to do is the following (high level):

1) read given vaccinations from table
2) order by date_given
3) assign ordinals to the rows in the order obtained by 2)
4) deduce missing shots by joining to another table that
defines the number of vaccinations in a schedule

This is a description of steps you decided would get you to your
goal. Instead of describing the steps, what's the goal?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991