__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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
