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.
> 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, (
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.
In response to
pgsql-sql by date
|Next:||From: Jie Liang||Date: 2004-06-14 23:32:57|
|Subject: Prepare Statement|
|Previous:||From: Stephan Szabo||Date: 2004-06-14 20:07:29|
|Subject: Re: query with =ALL |