Simpler Query [message #267517] |
Thu, 13 September 2007 10:46  |
MarcL
Messages: 455 Registered: November 2006 Location: Connecticut, USA
|
Senior Member |
|
|
Hi,
I'm having a brain freeze and hoping I can get some help.
Oracle Version: 9.2.0.7
Brief Scenario:
I have two tables both containing columns
time_stamp and updt_userid.
Using SQL I want to return the one record of the latest time_stamp and the updt_userid associated with that time_stamp.
Here is a sample test case.
SQL> create table marc_a(item_id,updt_userid varchar2(30), time_stamp date);
Table created
SQL> create table marc_b(item_id,updt_userid varchar2(30), time_stamp date);
Table created.
SQL> insert into marc_a(item_id,updt_userid,time_stamp) values (1,'USER1',sysdate);
1 row created.
1* insert into marc_a(item_id,updt_userid,time_stamp) values (2,'USER2',sysdate)
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> insert into marc_b(item_id,updt_userid,time_stamp) values (2,'USER3',SYSDATE);
1 row created.
SQL> commit;
select * from marc_a
UPDT_USERID TIME_STAMP ITEM_ID
------------------------------ ------------------- ----------
USER1 09/13/2007 07:58:02 1
USER2 09/13/2007 07:58:25 2
SQL> select * from marc_b;
UPDT_USERID TIME_STAMP ITEM_ID
------------------------------ ------------------- ----------
USER3 09/13/2007 07:58:59 2
So the result I would want from this is if looking for item_id 2
USER3 09/13/2007 07:58:59
If looking for item_id 1
USER1 09/13/2007 07:58:02
My head must be full of cobwebs. The following works but seems very unwieldy As in real life I will be joining the full PK of each table. Several columns each.
select time_stamp, updt_userid
from
(select updt_userid, time_stamp from marc_a where item_id=2
UNION
select updt_userid, time_stamp from marc_b where item_id=2)
where time_stamp = (select max(time_stamp) from
(select updt_userid, time_stamp from marc_a where item_id=2
UNION
select updt_userid,time_stamp from marc_b whereitem_id=2))
It just seems like there should be a neater way to do this
Thanks.
Marc
|
|
|
Re: Simpler Query [message #267520 is a reply to message #267517] |
Thu, 13 September 2007 10:52   |
Cthulhu
Messages: 381 Registered: September 2006 Location: UK
|
Senior Member |
|
|
Not tested, but something like this should do it:
select UPDT_USERID, TIME_STAMP, ITEM_ID
from
(select UPDT_USERID, TIME_STAMP, ITEM_ID, rank() over
(partition by item_id order by time_stamp desc) rn
from
(select * from marc_a
union all
select * from marc_b))
where rn=1
|
|
|
Re: Simpler Query [message #267522 is a reply to message #267517] |
Thu, 13 September 2007 10:55   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select * from marc_a order by item_id, time_stamp;
ITEM_ID UPDT_USERID TIME_STAMP
-------------------- ------------------------------ -------------------
1 USER1 13/09/2007 17:54:19
2 USER2 13/09/2007 17:54:21
2 rows selected.
SQL> select * from marc_b order by item_id, time_stamp;
ITEM_ID UPDT_USERID TIME_STAMP
-------------------- ------------------------------ -------------------
2 USER3 13/09/2007 17:54:22
1 row selected.
SQL> with
2 data as (
3 select item_id, updt_userid, time_stamp,
4 rank () over (partition by item_id order by time_stamp desc) rk
5 from ( select * from marc_a union all select * from marc_b )
6 )
7 select item_id, updt_userid, time_stamp
8 from data
9 where rk = 1
10 order by item_id
11 /
ITEM_ID UPDT_USERID TIME_STAMP
-------------------- ------------------------------ -------------------
1 USER1 13/09/2007 17:54:19
2 USER3 13/09/2007 17:54:22
2 rows selected.
Regards
Michel
|
|
|
Re: Simpler Query [message #267528 is a reply to message #267517] |
Thu, 13 September 2007 11:05  |
MarcL
Messages: 455 Registered: November 2006 Location: Connecticut, USA
|
Senior Member |
|
|
Thanks both .. rank seems to be the way to go, now I need to mesh it into my real query.
Appreciate it.
|
|
|