Date: Fri, 6 May 2005 09:25:35 +0200
From: Vortex <vortex25@gmx.de>
To: pgsql-sql@postgresql.org
Subject: select within aggregate?
Message-Id: <20050506092535.060bca35.vortex25@gmx.de>
Organization: private
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi!!

Of course the following is possible in various ways but i wonder
if there is an elegant and simple solution:

A part from apache log:
remote_host    varchar(50)
request_uri    varchar(50)
ts             timestamp with time zone

Now i want to see which host has done how many requests. This
is not difficult:

SELECT remote_host, count(*) FROM apache_log GROUP BY remote_host;

But now i would like to add a column to the result which
shows the most recent requested uri for each group.
This sounds quite easy at first glance but i see no simple solution.

I suppose with max(ts) i could acquire the time of the
most recent request within the group, but how do i get
the corresponding request_uri without doing subquerys or
something like that?

Thank you very much!

Klaus