X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9849ED1B19A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Jun 2004 17:44:19 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 24590-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 14 Jun 2004 20:44:16 +0000 (GMT)
Received: from jalapeno.crazydogs.org (jalapeno.jellybean.co.uk
	[212.78.70.100])
	by svr1.postgresql.org (Postfix) with ESMTP id 97DBBD1B172
	for <pgsql-sql@postgresql.org>; Mon, 14 Jun 2004 17:44:13 -0300 (ADT)
Received: from rp by jalapeno.crazydogs.org with local (Exim 3.36 #1 (Debian))
	id 1BZyJT-0001rn-00
	for <pgsql-sql@postgresql.org>; Mon, 14 Jun 2004 21:44:15 +0100
Date: Mon, 14 Jun 2004 21:44:15 +0100
From: Richard Poole <rp@guests.deus.net>
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Scalar subqueries
Message-ID: <20040614204415.GB6017@guests.deus.net>
References: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.com>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200406/129
X-Sequence-Number: 18074

On Mon, Jun 14, 2004 at 12:38:44PM -0700, Subbiah, Stalin wrote:
> 
> I've a logs table that has both sign-in and sign-out records which are
> differentiated by action flag. Records with action flag = (1,2) => sign-in
> records and action flag = (3,4,5,6,7) => sign-out records.

<snip>

> All I'm trying to do is print signin id and corresponding sign-out id's in
> single row.

You're writing one more level of sub-select than you actually need.

SELECT inlog.log_id AS signin_id, (
	SELECT MIN(outlog.log_id)
	FROM logs outlog
	WHERE outlog.log_id > inlog.log_id
	AND action IN (3, 4, 5, 6, 7)
) AS signout_id
FROM logs inlog
WHERE inlog.action IN (1, 2);

Assuming you want to match signins and signouts by the same account to
the same service, or whatever, you can add in clauses like

AND outlog.account_id = inlog.account_id

or whatever else you like, to the inner select.


Richard