Select 1 row from MANY

coldchillin
coldchillin used Ask the Experts™
on
I have a 1 to M table join but I only need a single value from the many table. Is this possible?

For instance:

People
PeopleID, PeopleName

Jobs
JobID, JobName

PeopleJob
PeopleID,JobID

SELECT PeopleName,JobName AS OneJobName
FROM People p
JOIN PeopleJob pj ON p.PeopleID = pj.PeopleID
JOIN Jobs j ON j.JobID = pj.JobID --how do I just get one value?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Yes, but which one?

SELECT p.PeopleName, max(j.JobName) AS OneJobName
FROM People p
JOIN PeopleJob pj ON p.PeopleID = pj.PeopleID
JOIN Jobs j ON j.JobID = pj.JobID
group by p.peoplename

or

Select peoplename, jobname
from
(
SELECT p.PeopleName, j.JobName AS OneJobName, row_number() over(partition by p.peopleID order by j.jobname) seq
FROM People p
JOIN PeopleJob pj ON p.PeopleID = pj.PeopleID
JOIN Jobs j ON j.JobID = pj.JobID
)
where seq = 1







Author

Commented:
I just need one of the job names!

I was able to get the first version working, but I'm more interested in the second solution you offered. I'm familiar with aggregates using group by, but I have no idea what the over(partition by) does!!

Is there any benefits to one over the other?
If it matters, my actual query is more involved. This is the inner query which uses multiple joins, which is then pivoted.

Commented:
Performance wise, I don't know.  But, logically, the partition by pattern is not aggregating; it is numbering within a group (partition) and then choosing. For example, you could identify the second highest salary which you cannot do with max().  The real nice thing about the partition by construct is that identifies the row of interest.  Unlike max(), for example, which requires a clumsy nested, correlated subuery in the where clause to identify the row containing the max value.  The equivalent partion by condition is trivial--> where seq = 1.

Get familiar with partition by, you will find is solves many thorny query problems and you will soon be living in despair when you need to work on a database that doesn't support it. :>)

 

Author

Commented:
I used the first option, worked like a charm. Will look into the second option.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial