2

Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.

If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.

Any way to do that?

SELECT DISTINCT
  Employees.ID, 
  Employees.FName,
  Employees.LName,
  Address.City,
  Address.State,
  Site.ID
from Employees
  Left Join Address on Employees.ID = Address.ID
  Left Join Site on Address.ADID = Site.ID
Where Site.ID in (
  SELECT Site.ID From Site Where Site.Type = 'Primary'
)
Order by Employees.ID, Site.ID

Example result set:

1234   John   Williams   Sacramento   CA   1  
1234   John   Williams   Portland     OR   2  
1111   Mary   Johnson    Sacramento   CA   1  
1112   James  Stoller    Carson City  NV   2  

Would like this back:

1234   John   Williams   Sacramento   CA   1  
1111   Mary   Johnson    Sacramento   CA   1  
1112   James  Stoller    Carson City  NV   2  
3

Wrap your query in a common table expression and add a row number column like this

SELECT DISTINCT
  Employees.ID, 
  Employees.FName,
  Employees.LName,
  Address.City,
  Address.State,
  Site.ID,
Row_Number() over(partition by Employees.ID order by Site.id) as rn

Then, select from the common table expression where rn = 1

| improve this answer | |
2

This can be achieved by joining the table Site twice:

SELECT e.ID
     , e.FName
     , e.LName
     , e.City
     , a.State
     , s.ID
  FROM Employees AS e
  JOIN Address   AS a ON a.ID = e.ID
  JOIN Site      AS s ON s.ID = a.ADID
  LEFT JOIN Site AS z ON z.ID = a.ADID -- second joined table
                     AND z.ID < s.ID   -- the smallest s.ID will get z.ID=NULL
 WHERE s.Type = 'Primary'
   AND z.ID IS NULL 
 ORDER BY e.ID ASC
;
| improve this answer | |
0
SELECT * FROM (
  -- create temp table [AllEmpTable] of all of the values
  SELECT DISTINCT 
    Employees.ID, 
    Employees.FName,
    Employees.LName,
    Address.City,
    Address.State,
    Site.ID,
    -- adds a column [RN] counting the number of rows held by the employee order by the SiteID
    Row_Number() over(partition by Employees.ID order by Site.ID) as RN 
  from Employees
  Left Join Address on Employees.ID = Address.ID
  Left Join Site on Address.ADID = Site.ID 
    and Site.Type = 'Primary'
) as [AllEmpTable]
Where [AllEmpTable].RN = 1  -- I only care about the first row number RN for the employee in the temp table [AllEmpTable]
Order by Employees.ID, Site.ID

Works great, at least until someone messes up the site numbering scheme, hopefully no longer my problem by then. Also gives me more flexibility on further filtering the results if needed in the future.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.