[Babase] Re: query weirdness again
Karl O. Pinc
kop at meme.com
Wed Jul 19 15:12:16 EDT 2006
On 07/19/2006 01:33:07 PM, Leah Gerber wrote:
> Hello Karl,
>
> I am trying to select the latest hybrid score for each sname (some
> have more than one). This query works in Foxpro but not in PPA. Any
> ideas?
>
> SELECT MAX(date), type, sname, score, observers FROM hybrids group
> BY sname;
The error you get is:
ERROR: column "hybrids.type" must appear in the GROUP BY clause or be
used in an aggregate function
In other words, you're asking for lines that summarize many lines
of data but when it comes to the "type" (and the "score" and
"observers") column(s) you haven't said how the get the summary
from all the lines that go with the requested sname. The computer
does not know that there's only one row in the table with a
given sname and a particular MAX(date) value. So, it's asking that
you put the "type" (and "score" and "observers") into the GROUP
BY so that it's sure you'll only get 1 possible answer for these
data values. However, this is not what you want. There are
(possibly) multiple "type" (and "score" and "observers" values)
for any one sname, and you only want the one that goes with
the particular MAX(date)/sname combination.
The answer is to first find the MAX(date) and then use that to get
the data you want.
You could do:
SELECT MAX(date) as date, sname
INTO TEMPORARY TABLE maxdates
FROM hybrids
GROUP BY sname;
SELECT hybrids.date
, hybrids.type
, hybrids.sname
, hybrids.score
, hybrids.observers
FROM hybrids, maxdates
WHERE hybrids.sname = maxdates.sname
AND hybrids.date = maxdates.date
ORDER BY hybrids.sname;
(Note: because ppa makes a new connection each time
you execute a query, there's no need to do
a "DROP TABLE maxdates;" at the end. If you were
working in psql at the Unix command line you might
want to clean up and do the DROP because it keeps
one connection until you leave psql.)
But there is a shorthand way to do this by "aliasing" the
table name and joining the table to itself with a subquery.
Like so:
SELECT date, type, sname, score, observers
FROM hybrids
WHERE date = (SELECT MAX(date)
FROM hybrids AS maxdates
WHERE maxdates.sname = hybrids.sname)
ORDER BY sname;
Potentially, this is faster, and it's certainly less to
type and more clear when you read it. But, you have to
know about aliasing table names when you write it.
PostgreSQL 8.1.4 Documentation
2.6. Joins Between Tables (for "aliasing" table names)
http://www.postgresql.org/docs/8.1/static/tutorial-join.html
2.7. Aggregate Functions (for subqueries)
http://www.postgresql.org/docs/8.1/static/tutorial-agg.html
Karl <kop at meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
More information about the Babase
mailing list