From: Subbiah, Stalin <SSubbiah@netopia.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Scalar subqueries
Date: Mon, 14 Jun 2004 12:38:44 -0700
Message-ID: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.com> (raw)
__sorry for posting it second time__
Not sure what am I missing. I really appreciate if anyone could point it out
to me.
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.
playfield=# select * from logs;
log_id | log_creation_date | account_id | user_id | service | action |
mac
--------+---------------------+------------+---------+---------+--------+---
----------
1 | 2004-04-29 10:48:36 | Robert | 3 | 5 | 2 |
00-00-00-00
3 | 2004-04-29 10:53:44 | Robert | 3 | 5 | 3 |
00-00-00-00
5 | 2004-04-29 11:11:35 | Robert | 3 | 5 | 1 |
00-00-00-00
1003 | 2004-05-03 15:18:53 | Robert | 3 | 5 | 5 |
00-00-00-00
1004 | 2004-05-03 15:19:50 | Robert | 8 | 5 | 1 |
00-00-00-00
(5 rows)
All I'm trying to do is print signin id and corresponding sign-out id's in
single row.
Select I wrote :
select log_id as signin_id,
(select foo.log_id
from
(select foo1.log_id
from logs as foo1
where foo1.action in (3,4,5,6,7)
and l.log_id > foo1.log_id
order by foo1.account_id, foo1.user_id, foo1.mac,
foo1.log_creation_date) as foo limit 1) as signout_id
from logs as l
where action in (1,2);
Gives...
signin_id | signout_id
-----------+------------
1 |
5 | 3
1004 | 3
(3 rows)
Expected output :
signin_id | signout_id
-----------+------------
1 | 3
5 | 1003
1004 |
(3 rows)
Thanks,
Stalin
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: SSubbiah@netopia.com
Subject: Re: Scalar subqueries
In-Reply-To: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.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