Select distinct values from seperate tables

Question

Select distinct values from seperate tables

Douwe

SSC Enthusiast

Points: 101
More actions
May 17, 2005 at 1:47 am

#164644

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 ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

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 !!!**

sara karasik SSCommitted Points: 1573 More actions · Answer 2

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&nbsp dt

on dt.ResJoinID = djn.ResJoinID

and dt.LatestDate = djn.ChangeDate

ORDER BY MemberID , djn.ChangeDate DESC

Let me know if this worked.

Douwe SSC Enthusiast Points: 101 More actions · Answer 3

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

chris webster SSCommitted Points: 1974 More actions · Answer 4

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

&nbsp as ChangeDate

from SAVA.SAVA_SAVDJN a

where a.ResJoinID = 2

&nbsp b

on 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?!?!?!

sara karasik SSCommitted Points: 1573 More actions · Answer 5

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