From: | Mark Dalphin <mdalphin(at)amgen(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem with a GROUP BY clause |
Date: | 1999-08-01 00:52:08 |
Message-ID: | 37A39A37.ACB63308@amgen.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am running Postgresql ver 6.5.1 on an SGI (Irix).
I am trying to write a query to return some top scoring items, using a
GROUP BY clause, however, I am having trouble with "empty" results. For
example:
htg=> SELECT LocusID, Score FROM Match
htg-> WHERE SeqID=4358;
locusid|score
-------+-----
(0 rows)
In the case of SeqID=4358, there are no matching LocusIDs, so 0 rows are
returned. This is fine. There may be cases, however, when there are many
matching LocusIDs, so I use a GROUP BY clause. This usually works, but in cases
like the above one, where there are no matching LocusIDs, it returns the wrong
result. Ie:
htg=> SELECT LocusID, max(Score) FROM Match
htg-> WHERE SeqID=4358
htg-> GROUP BY LocusID;
locusid|max
-------+---
|
(1 row)
There is no row, yet Postgresql reports there is "one" which it returns as
empty. This also makes the Perl DBI::DBD interface unhappy. It seems to return
an undefined value which can't even be tested with "if(! defined $value)"!
Unless I really misunderstand "GROUP BY", I think this is a bug.
Mark
--
Mark Dalphin email: mdalphin(at)amgen(dot)com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
From | Date | Subject | |
---|---|---|---|
Next Message | astromonk | 1999-08-01 01:26:06 | Install problems (libpq.so.1) |
Previous Message | Gilles Darold | 1999-08-01 00:43:32 | Re: [GENERAL] Search |