asked on

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

ASKER CERTIFIED SOLUTION

Member_2_861731

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

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

anglandp

ASKER

this did it perfectly. Thank you kindly!