SQL - Help with a Query

I have an employees table (PS_EMPLOYEES) from which I'm pulling the following
- employeeID (primary key)
- name
- user name
- title

I have a job table (PS_JOBS) from which I'm pulling the following
- employeeID (primary key)
- FileNumber

When the results come back, some employees are listed twice, because in the jobs table they have more then one file number. I need the record which has the larger file number. How would I adjust the query below to do that? Thanks!

SELECT distinct    PS_EMPLOYEES.EMPLID, PS_EMPLOYEES.NAME, PS_EMPLOYEES.FIRST_NAME, PS_EMPLOYEES.MIDDLE_NAME, PS_EMPLOYEES.LAST_NAME,
                      PS_EMPLOYEES.BUSINESS_TITLE, PS_JOB.FILE_NBR
FROM         PS_EMPLOYEES LEFT OUTER JOIN
                      PS_JOB ON PS_EMPLOYEES.EMPLID = PS_JOB.EMPLID
anglandpAsked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
You could use a MAX and a group by to do this.
Something like:

SELECT PS_EMPLOYEES.EMPLID, PS_EMPLOYEES.NAME, PS_EMPLOYEES.FIRST_NAME, PS_EMPLOYEES.MIDDLE_NAME, PS_EMPLOYEES.LAST_NAME,
                      PS_EMPLOYEES.BUSINESS_TITLE, MAX(PS_JOB.FILE_NBR) FILE_NBR
FROM         PS_EMPLOYEES LEFT OUTER JOIN
                      PS_JOB ON PS_EMPLOYEES.EMPLID = PS_JOB.EMPLID
GROUP BY PS_EMPLOYEES.EMPLID, PS_EMPLOYEES.NAME, PS_EMPLOYEES.FIRST_NAME, PS_EMPLOYEES.MIDDLE_NAME, PS_EMPLOYEES.LAST_NAME,
                      PS_EMPLOYEES.BUSINESS_TITLE


*EDIT* Removed DISTINCT
0
 
HainKurtSr. System AnalystCommented:
use row_number

select * from (
SELECT PS_EMPLOYEES.EMPLID, PS_EMPLOYEES.NAME, PS_EMPLOYEES.FIRST_NAME, PS_EMPLOYEES.MIDDLE_NAME, PS_EMPLOYEES.LAST_NAME, 
                      PS_EMPLOYEES.BUSINESS_TITLE, PS_JOB.FILE_NBR,
row_number() over(partition by PS_EMPLOYEES.EMPLID order by FILE_NUMBER desc) rn
FROM         PS_EMPLOYEES LEFT OUTER JOIN
                      PS_JOB ON PS_EMPLOYEES.EMPLID = PS_JOB.EMPLID
) x where rn=1

Open in new window

0
 
anglandpAuthor Commented:
this did it perfectly. Thank you kindly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.