From: Richard Poole <rp@guests.deus.net>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Scalar subqueries
Date: Mon, 14 Jun 2004 21:44:15 +0100
Message-ID: <20040614204415.GB6017@guests.deus.net> (raw)
In-Reply-To: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.com>
References: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.com>
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
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: rp@guests.deus.net
Subject: Re: Scalar subqueries
In-Reply-To: <20040614204415.GB6017@guests.deus.net>
* 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