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
**********************************************************************
