Thread: Problem with aggregate functions and GROUP BY

I'm having real trouble with aggregate functions. I have a table which
tracks the value of a field in another table over time - it contains a
foreign key to the row in the other table, the value, and a timestamp to
sort by. I'm trying to write a query that returns the rows containing the
most recent values for each foreign key.

The table looks like this: (fixed width fonts required, and this is a test
table):

# SELECT * FROM test ORDER BY fk, sortby;fk | value | sortby
----+-------+-------- 1 |     2 |      1 1 |     0 |      2 1 |     4 |      3 2 |     2 |      1

Now obviously, what I'm aiming for is:
fk | value | sortby
----+-------+-------- 1 |     4 |      3 2 |     2 |      1

which is the highest value of 'sortby' for each value of 'fk'.

I would think that the SQL to achieve this is:
   SELECT * FROM test GROUP BY fk HAVING sortby = MAX(sortby);

but running this in psql gives:
   ERROR:  Attribute test.value must be GROUPed or used in an aggregate
function

No matter what I do to the query, it gives me this message for test.value
and test.sortby, unless I GROUP BY all three of them, which doesn't give me
the result I want. I'm completely stumped - can anyone help?

Thanks in advance,

Alex
--
Alex Page, IT Department, Solid State Logic
E-Mail: alex.page@solid-state-logic.com
Phone: +44 (0) 1865 842 300
Web: http://www.solid-state-logic.com



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

"Alex Page" <alex.page@solid-state-logic.com> writes:
> I'm trying to write a query that returns the rows containing the
> most recent values for each foreign key.

The best way to do this (on every dimension except portability :-()
is SELECT DISTINCT ON.  See the weather-report example in the SELECT
reference page.
        regards, tom lane

Alex,

> which is the highest value of 'sortby' for each value of 'fk'.
> 
> I would think that the SQL to achieve this is:
> 
>     SELECT * FROM test GROUP BY fk HAVING sortby = MAX(sortby);

You need a subselect for this:

SELECT test.*
FROM test, (SELECT max(sortby) as maxsort, fk            FROM test           GROUP BY fk) maxtest
WHERE test.fk = maxtest.fk AND test.sortby = maxtest.maxsort;

There's also a pgsql extension called SELECT DISTINCT ON, but it's not
SQL 92 standard so I don't reccomend it.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco