Thnaks for replying Nello
 
"Group by" is out of the question because of the joins....
 
I ended up just sorting it out progammatically after ordering by date.  Probably just as messy but was within my capabilities so there it stands.  Its nice to know that I wasnt just being a dumb newbie not being able to sort it out with SQL .  =) 
 
Cheers for your time anyway
 
----- Original Message -----
Sent: Wednesday, February 12, 2003 2:07 PM
Subject: Re: [DUG]: sql help please

i was waiting a bit for a more sage response, but ...
could someone help me with my query please?
 
 I have the following tables
 
/* snip ... */
 
Some skills are required in more than one job.  If this person has both those jobs and doesnt have that skill, then the query returns the skill for as many times as it is needed.
 
What i really want, is for the skill itsself to be only returned once, with the earliest timeperiod.  Is this possible in SQL or do i need to frig with code? (distinct isnt working with all my joins)
 
i think the problem with the DISTINCT might not be "all the joins", but instead that that different time periods make rows with the same skill different so they both show up.  
 
in similar situations i've handled this in 2 different ways.
 
1) use GROUP BY on all the distinct columns with MIN() on the time period column.  
 
2) write a SP (in IB or Oracle where SPs can return rowsets) or a Function (in MSSQL after 7.0) that does the select with ORDER BY on the time period , fetchs one (the first) row into the return arguments of the SP and then returns.
 
neither very elegant i'm afraid, but maybe you will find it useful
 
 
 
 
 

Reply via email to