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
