0

I was wondering if anyone could help.

I am trying to write some code that returns a list of the latest hires based upon Jobtitle using the Adventureworks2012 databse.

So far, I have the following:

    SELECT DISTINCT HREDH.BusinessEntityID,
    HRE.JobTitle,
    hre.HireDate
FROM [HumanResources].[EmployeeDepartmentHistory] HREDH
INNER JOIN HumanResources.Employee HRE ON HREDH.BusinessEntityID = HRE.BusinessEntityID
    AND hre.BusinessEntityID = (
        SELECT TOP 1 BusinessEntityID
        FROM HumanResources.Employee hre2
        WHERE hre2.JobTitle = hre.JobTitle
        ORDER BY HireDate DESC
        )
ORDER BY HRE.JobTitle

This appears to work fine, but I am sure there is a better way to do it (without the use of SELECT DISTINCT at the beginning of the statement)

I am trying my best to learn SQL by myself, so any help from the vast pool of knowledge on here would be greatly appreciated!

Thanks,

Rosscoasks
  • 97
  • 9

1 Answers1

0

This will return all rows from the join of the two tables where the hire date for the job title is equal to the highest date for that job title.

SELECT
    HREDH.BusinessEntityID
    ,HRE.JobTitle
    ,hre.HireDate
FROM [HumanResources].[EmployeeDepartmentHistory]  AS HREDH
    JOIN HumanResources.Employee AS HRE
        ON HREDH.BusinessEntityID = HRE.BusinessEntityID
WHERE HRE.HireDate = (SELECT MAX(HireDate) FROM HumanResources.Employee AS _HRE WHERE HRE.JobTitle = _HRE.JobTitile)
ORDER BY HRE.JobTitle
Anthony Hancock
  • 911
  • 6
  • 13
  • Thank you, Anthony! This seems to do most of the work, but it still returns duplicate records when I execute the query. This is the reason why I had to use DISTINCT statement in my query. Do you know of an alternate way to remove the duplicates? – Rosscoasks Apr 05 '17 at 20:57
  • Without downloading and installing the AdventureWorks database, my guess would be that there are cases where more than one person is hired for the same job on the same day. If that's the case, then using distinct is correct because those aren't duplicate returns, you just aren't interested in seeing the data multiple times. Alternately if you can provide the name of the primary key on the Employee table I can edit my code for another way of doing it where you would get the most recent employee determined by highest primary key value. – Anthony Hancock Apr 05 '17 at 21:26