Message-ID: <F1F84A2E5F9EBD46A9BDB905EB5B10393A9A@mxca1.netopia.com>
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
MIME-Version: 1.0
Content-Type: text/plain

__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