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
Requires Free Membership to View
*
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 *
from ENTITY T1, ACCOUNT T3
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;
This was first published in November 2011
Join the conversationComment
Share
Comments
Results
Contribute to the conversation