skex skex - 1 year ago 56
SQL Question

Oracle database -- how can I combine these two tables so that the where clause looks at multiple values?

Really not sure how to word this. I have two tables, t1 and t2. t1 contains a very long history of messages, so it has [message, t1_id, t2_d]. t2, for the sake of the example just has t2_id. I want to find the message correlated with the highest t1_id. That's easy

select message
from t1
where t1_id = (
select MAX(t1_id) as mx
from t1
where t2_id = 1

But as you can see, I have hard coded the t2_id. My intention is for this code (which currently just returns the highest message from the one id) to do so an ALL distinct id's. There's a few ways to do this, but I know you can do

select t2_id
from t2;

So how can I essentially replace the hard coding with the above select statement? I know I can't directly, but even using GROUP BY I was having no luck.

Clarification: the t2_id column in t1 correlates to the t2_id column in t2. So in t1 there will be many repeats of t2_id's from t2. So I just want the message for EACH t2_id in t1 that has the highest t1_id. So t1 has thousands of rows but only about 20 different t2_id's, so I should have 20 row result.

Answer Source

A more efficient solution (probably the most efficient one), using slightly more advanced features.

select t2_id,                     -- you probably need this! 
       max(t1_id) as max_t1_id,   -- if needed
       max(message) keep (dense_rank last order by t1_id) as message
from t1
group by t2_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download