Home » SQL & PL/SQL » SQL & PL/SQL » Simpler Query
Simpler Query [message #267517] Thu, 13 September 2007 10:46 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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.
Previous Topic: UNION RELATED
Next Topic: Using a stored proc through a db-link, in a package, on a pass-through-account?
Goto Forum:
  


Current Time: Sun Dec 04 08:43:05 CST 2016

Total time taken to generate the page: 0.13534 seconds