Select distinct values from seperate tables
-
Hi,
I have a table that contains dates for resignment and joining. The reasons for joining/ resigning is in a seperate table.
Now I only want the last date of a person, I thought that the distinct and the order by Field DESC whould do the trick, but it doesn't. It still shows all the values where djn.ResJoinID = 2. Any help?
Select Distinct(MemberID), djn.ResJoinID, djn.Reason, djn.ChangeDate from SAVA.SAVA_SAVRSN rsn INNER JOIN SAVA.SAVA_SAVDJN djn ON (djn.ResJoinID = rsn.ResJoinID) AND djn.ResJoinID = 2 ORDER BY MemberID , djn.ChangeDate DESC
Help anyone ?
-
Are you trying to get the resigned date for one particular person - or are you trying to get the names of all people who resigned on a particular date ?!
Also - would I be right in assuming that for each person there would be 2 corresponding rows - one on date joined and reason and another on date resigned and reason ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Let's reorganize the logic here. We need to limit the results to the latest date for each member, so we will use a derived table as a sub-query. I've added the sub-query to your code, and split the lines for increased readability.
Select MemberID,
djn.ResJoinID,
djn.Reason,
djn.ChangeDate
from SAVA.SAVA_SAVRSN rsn
INNER JOIN SAVA.SAVA_SAVDJN djn
ON djn.ResJoinID = rsn.ResJoinID
AND djn.ResJoinID = 2
join (select ResJoinID, max(ChangeDate) as LatestDate
from SAVA.SAVA_SAVDJN 
dton dt.ResJoinID = djn.ResJoinID
and dt.LatestDate = djn.ChangeDate
ORDER BY MemberID , djn.ChangeDate DESC
Let me know if this worked.
-
Hi,
I am sorry for the unclearness of this matter, but I believe you have summed it up quite nicely.
I am having trouble with this subquery part:
Join
(select ResJoinID, max(ChangeDate) as LatestDate from SAVA.SAVA_SAVDJN ) dt
on dt.ResJoinID = djn.ResJoinID
and dt.LatestDate = djn.ChangeDate
ORDER BY MemberID , djn.ChangeDate DESC
It says that the cols ResJoinID,ChangeDate is invallid in the select clause, because it is not contained in an arragate function, and in a order by... ?
Thanks for the help
-
Give this a go
select rsn.MemberID,
b.ResJoinID,
b.Reason,
b.ChangeDate
from SAVA.SAVA_SAVRSN rsn
inner join
(
select distinct
a.ResJoinID,
c.Reason,
(
select max(ChangeDate)
from SAVA.SAVA_SAVDJN
where ResJoinID = a.ResJoinID
 
as ChangeDatefrom SAVA.SAVA_SAVDJN a
where a.ResJoinID = 2
 
bon rsn.ResJoinID = b.ResJoinID
order by rsn.MemberID , b.ChangeDate desc
not to sure whether this is optimal, anybody any other ideas?
Cheers
ps my preview shows automatic replacement of brackets with emoticons?!?!?!
-
Oops!
I do apologize. I left out the crucial GROUP BY ResJoinID.
This is what is needed:
Join
(select ResJoinID, max(ChangeDate) as LatestDate
from SAVA.SAVA_SAVDJN
GROUP BY ResJoinID
) dt
on dt.ResJoinID = djn.ResJoinID
and dt.LatestDate = djn.ChangeDate
ORDER BY MemberID , djn.ChangeDate DESC
Please let me know if this works now.
Sara
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply