From: Bruno Wolff III <bruno@wolff.to>
To: Vortex <vortex25@gmx.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: select within aggregate?
Date: Fri, 6 May 2005 07:49:23 -0500
Message-ID: <20050506124923.GB14417@wolff.to> (raw)
In-Reply-To: <20050506092535.060bca35.vortex25@gmx.de>
References: <20050506092535.060bca35.vortex25@gmx.de>
On Fri, May 06, 2005 at 09:25:35 +0200,
Vortex <vortex25@gmx.de> wrote:
> 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?
You could use max(ts) as long as you can have two requests occur at the
same time. This might be a reasonable assumption, but it might have been
better to include a sequence so that each record would be guaranteed to
have a unique key.
Using DISTINCT ON to get the latest URIs is probably better. You can
join that query to the one doing the count. This will probably be more
efficient than executing a subquery for each remote host.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, vortex25@gmx.de
Subject: Re: select within aggregate?
In-Reply-To: <20050506124923.GB14417@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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