From: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
To: Vortex <vortex25@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Re: select within aggregate?
Date: Fri, 6 May 2005 13:34:26 +0530
Message-ID: <02767D4600E59A4487233B23AEF5C59922C2AB@blrmail1.aus.pervasive.com> (raw)
Hi
create table abc
(
remote_host varchar(50),request_uri varchar(50),ts timestamp
);
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
where a.remote_host = abc.remote_host and abc.ts = 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)
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: ramakrishnanm@pervasive-postgres.com, vortex25@gmx.de
Subject: Re: select within aggregate?
In-Reply-To: <02767D4600E59A4487233B23AEF5C59922C2AB@blrmail1.aus.pervasive.com>
* 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