Skip site navigation (1) Skip section navigation (2)

Re: Scalar subqueries

From: Richard Poole <rp(at)guests(dot)deus(dot)net>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Scalar subqueries
Date: 2004-06-14 20:44:15
Message-ID: 20040614204415.GB6017@guests.deus.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Jie LiangDate: 2004-06-14 23:32:57
Subject: Prepare Statement
Previous:From: Stephan SzaboDate: 2004-06-14 20:07:29
Subject: Re: query with =ALL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group