pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedScalar subqueries
3+ messages / 2 participants
[nested] [flat]
* Scalar subqueries
@ 2004-06-14 19:33 Subbiah, Stalin <SSubbiah@netopia.com>
0 siblings, 0 replies; 3+ messages in thread
From: Subbiah, Stalin @ 2004-06-14 19:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Scalar subqueries
@ 2004-06-14 19:38 Subbiah, Stalin <SSubbiah@netopia.com>
0 siblings, 1 reply; 3+ messages in thread
From: Subbiah, Stalin @ 2004-06-14 19:38 UTC (permalink / raw)
To: pgsql-sql
__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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Scalar subqueries
@ 2004-06-14 20:44 Richard Poole <rp@guests.deus.net>
parent: Subbiah, Stalin <SSubbiah@netopia.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Poole @ 2004-06-14 20:44 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-14 20:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-14 19:33 Scalar subqueries Subbiah, Stalin <SSubbiah@netopia.com>
2004-06-14 19:38 Scalar subqueries Subbiah, Stalin <SSubbiah@netopia.com>
2004-06-14 20:44 ` Richard Poole <rp@guests.deus.net>
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