Thread: row numbering
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 Thanks in advance, -- Sinceramente, Josué Maldonado. ... "El bien supone la luz, el mal tinieblas. Cada vez hay menos iluminación."
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
On Fri, 2005-02-25 at 16:44 -0600, 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
>
> Thanks in advance,
>
>
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
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/
> 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 > 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> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
> > > > 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
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/
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: > > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
> 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. > > 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
> > 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
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
> 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", I want to be able to display "shot 4: ..." and "shot 5: ..." in the application but pull the data from the database, not calculate it in the application. Reason being that there are/can be several different clients that should all be getting this right. Our current view does just that but only with shots already given. > or just that the patient needs to be shot two more times, No, that would simply be select max(shots in schedule) - count(shots given) where patient = <someone> > or do you really want the *application dates*? No. Those are under more conditions than the database should have to handle. IOW I would not suggest putting *that* much business logic into the database. There would be some value in getting the *by-schedule* next application date but that's of secondary importance to me. > This is a description of steps you decided would get you to your > goal. Instead of describing the steps, what's the goal? Sure, fine. I want a view that roughly looks like this: pk_patient schedule vacc_no given 1 Tet 1 1.1.1980 1 Tet 2 1.1.1985 1 Hep 1 1.1.1980 1 Hep 2 NULL -> IOW missing 2 ... ... ... Why is there no missing Tet shot ? Because the schedule does not define more than 2 shots. Medically, this is a bad example because Tet actually requires more and also requires a booster every 10 years but that does not make a difference to the problem at hand. The less technical goal is: I want the database to be able to provide a vaccination *status* view to me. Also notice that we do have views that display the missing shots per schedule per patient. I just have not found a way to join the two views (that is, given and missing) because that would AFAICT require the output "row numbering". You can look up our complete schema in our Wiki: http://salaam.homeunix.com/twiki/bin/view/Gnumed/WebHome Go to Deverloper Guide -> Database Structure. Karsten Hilbert, MD GnuMed i18n coordinator -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
> You can look up our complete schema in our Wiki: > > http://salaam.homeunix.com/twiki/bin/view/Gnumed/WebHome > > Go to Deverloper Guide -> Database Structure. http://salaam.homeunix.com/twiki/bin/view/Gnumed/DatabaseSchema is more convenient for you guys. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Mar 10, 2005 at 13:22:05 +0100, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > > Also notice that we do have views that display the missing > shots per schedule per patient. I just have not found a way to > join the two views (that is, given and missing) because that > would AFAICT require the output "row numbering". If you have a view of patients shots and a view of patients missing shots, the way to connect them is with a union. You can then group or order the union to display data about a patient in one place. Another approach would to cross join the patients with the required shots and then left join that result with the shots patients have had.