pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with aggregate functions and GROUP BY
3+ messages / 3 participants
[nested] [flat]

* Problem with aggregate functions and GROUP BY
@ 2001-08-08 16:16 Alex Page <alex.page@solid-state-logic.com>
  2001-08-08 17:26 ` Re: Problem with aggregate functions and GROUP BY Tom Lane <tgl@sss.pgh.pa.us>
  2001-08-08 20:04 ` Re: Problem with aggregate functions and GROUP BY Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Alex Page @ 2001-08-08 16:16 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Problem with aggregate functions and GROUP BY
  2001-08-08 16:16 Problem with aggregate functions and GROUP BY Alex Page <alex.page@solid-state-logic.com>
@ 2001-08-08 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2001-08-08 17:26 UTC (permalink / raw)
  To: Alex Page <alex.page@solid-state-logic.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Problem with aggregate functions and GROUP BY
  2001-08-08 16:16 Problem with aggregate functions and GROUP BY Alex Page <alex.page@solid-state-logic.com>
@ 2001-08-08 20:04 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2001-08-08 20:04 UTC (permalink / raw)
  To: Alex Page <alex.page@solid-state-logic.com>; +Cc: pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-08-08 20:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-08 16:16 Problem with aggregate functions and GROUP BY Alex Page <alex.page@solid-state-logic.com>
2001-08-08 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-08 20:04 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox