content-class: urn:content-classes:message
Subject: Re: select within aggregate?
Date: Fri, 6 May 2005 13:34:26 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-ID: 
 <02767D4600E59A4487233B23AEF5C59922C2AB@blrmail1.aus.pervasive.com>
Thread-Topic: [SQL] select within aggregate?
Thread-Index: AcVSDUmRv9WrWI3IQfaer5p5CmQDSwABVGQg
From: "Ramakrishnan Muralidharan" <ramakrishnanm@pervasive-postgres.com>
To: "Vortex" <vortex25@gmx.de>, <pgsql-sql@postgresql.org>

Hi

create table abc
(
remote_host    varchar(50),request_uri    varchar(50),ts timestamp=20
);

SELECT abc.remote_host ,  c , abc.request_uri , a.t FROM abc , ( select =
remote_host , count(*) as c , max( ts ) as t from abc group by =
remote_host ) as a=20
where a.remote_host =3D abc.remote_host and abc.ts =3D a.t

Regards,
R.Muralidharan

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Vortex
Sent: Friday, May 06, 2005 12:56 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] select within aggregate?


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)