Problem solve Get help with specific problems with your technologies, process and projects.

Using inline view to better query joined tables

One reader asks how to best query data from two joined Oracle tables.

I have a table called ENTITY with columns srcID, entID, occurrence and histflag. I want to get the rows with the max value in column 'occurrence' for each entID.  I have accomplished this with the following:

select *
from ENTITY T1
where T1.srcID  = 2          
and   T1.histflag = 'N'          
and   T1.occurrence =
      (select max(T2.occurrence)
       from ENTITY T2
       where T2 .srcID =   T1.srcID
       and    T2 .entID =   T1.ent_id);

I am able to get the row for each entID that has the highest value in the column 'occurrence'. But once I get the max row for each entID, I want to join this entity row to the ACCOUNT table to grab some additional data.  This is my select statement:

select *
where T1.srcID  = 2          
and   T1.histflag = 'N'          
and   T1.occurrence =
      (select max(T2.occurrence)
       from ENTITY T2
       where T2 .srcID =   T1.srcID
       and    T2 .entID =   T1.ent_id)
and T1.srcID =   T3.srcID
and T1.entID =   T3.ent_id);

I am not getting my expected results.  I will need to join other tables to this as well as I am pulling data from many source tables together. I need to get this first one working before I can proceed. Thanks so much.

What you can do is move the query to get the latest entity into an inline view. Then it should be much easier to add the other tables to this result set, so long as they have a valid relationship, of course.

select E.*, A.*
from (
select T1.*
from ENTITY T1
where T1.srcID  = 2          
and   T1.histflag = 'N'          
and   T1.occurrence =
      (select max(T2.occurrence)
       from ENTITY T2
       where T2 .srcID =   T1.srcID
       and    T2 .entID =   T1.entID)) E
INNER JOIN account A
ON A.entID = E.entID;

Dig Deeper on Oracle and SQL

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

I like the way this was done..

another way is to use a with clause

maxent as
(select max(occurrence) maxo,
from entity
where histflag = 'N'
and srcID = 2
group by entID)
select /*+ ordered use_nl(me ent a) */
ent.*, a.*
from maxent me
join entity ent on (ent.occurrence = me.maxo and
ent.srcID = 2 and
ent.entId = me.entID and
ent.histflag = 'N')
join account a on (a.entId = me.entID)

for best performance make sure there are indexes on
--> entity (srcID, histflag, entID, occurrence)
--> account (entID)

the hint may not be needed, but I find it works well in these cases.