Selecting Only One Row to Return From a Set of Rows?

Question

I am practicing SQL using the AdventureWork2012 database and I have created a table of employee information. Here is my query and the result.

SELECT 
    [e].[BusinessEntityID]
    , [e].[JobTitle]
    , CONCAT (p.FirstName, ' ', p.LastName)             AS Full_Name
    , dep.Name                                          AS Department

    , DATENAME(dw, edh.StartDate) + ', ' +
      DATENAME(month, edh.StartDate) + ' ' +
      DATENAME(day, edh.StartDate) + ', ' +                 
      DATENAME(year, edh.StartDate)                     AS 'Start_Date'

    , CASE 
        WHEN edh.EndDate IS NULL
        THEN CONVERT(VARCHAR, GETDATE(), 101)       
        ELSE edh.EndDate
      END                                                                       AS End_Date

    , IIF(LEFT(pp.PhoneNumber, 3) = '1 (', ' ', LEFT(pp.PhoneNumber, 3))        AS Area_Code
    , eph.Rate

    , CASE CONVERT(CHAR(10), eph.PayFrequency)
        WHEN 2 THEN 'Biweekly'
        ELSE 'Weekly'
      END                                               AS PayFrequency

    , IIF(e.SalariedFlag = 1, 'Y', 'N')                 AS 'SalaryEmployee'

    , CASE 
        WHEN e.SalariedFlag = 1
        THEN ROUND((eph.Rate*40)*52, 2)
        ELSE NULL
      END                                               AS YearlySalary
--INTO EmpInfo

FROM [HumanResources].[Employee]    AS e
INNER JOIN  [HumanResources].[EmployeeDepartmentHistory] AS edh
    ON edh.BusinessEntityID = e.BusinessEntityID
INNER JOIN [Person].[Person] AS p 
    ON p.BusinessEntityId = e.BusinessEntityID
INNER JOIN [Person].[PersonPhone] AS pp
    ON pp.BusinessEntityID = e.BusinessEntityID
INNER JOIN [HumanResources].[Department] AS dep
    ON dep.DepartmentID = edh.DepartmentID
INNER JOIN [HumanResources].[EmployeePayHistory] AS eph
    ON eph.BusinessEntityID = e.BusinessEntityID


ORDER BY e.BusinessEntityID;

BusinessEntityID    JobTitle    Full_Name   Department  Start_Date  End_Date Area_Code  Rate    PayFrequency    SalaryEmployee  YearlySalary
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1   Chief Executive Officer         Ken Sánchez  Executive      Saturday, February 15,  2003 2019-11-08  697  125.50    Biweekly    Y   261040.00
2   Vice President of Engineering   Terri Duffy  Engineering    Sunday, March 3, 2002   2019-11-08  819 63.4615 Biweekly    Y   131999.92
3   Engineering Manager Roberto Tamburello  Engineering Wednesday, December 12, 2001    2019-11-08  212 43.2692 Biweekly    Y   89999.94
4   Senior Tool Designer    Rob Walters  Engineering    Saturday, January 5, 2002   2004-06-30  612 8.62    Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Engineering    Saturday, January 5, 2002   2004-06-30  612 23.72   Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Engineering    Saturday, January 5, 2002   2004-06-30   612    29.8462 Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Tool Design     Thursday, July 1, 2004  2019-11-08  612    8.62    Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Tool Design     Thursday, July 1, 2004  2019-11-08  612    23.72   Biweekly    N   NULL
4   Senior Tool Designer    Rob Walters  Tool Design    Thursday, July 1, 2004   2019-11-08  612    29.8462  Biweekly   N   NULL
5   Design Engineer Gail Erickson   Engineering     Wednesday, February 6, 2002  2019-11-08  849    32.6923  Biweekly   Y   67999.98

As you can see, Rob Walters is listed 6 times because he got a raise 3 times as an employee and is part of two departments. Is there a way I can only return Rob Walter's latest/highest hourly rate for the two departments he is in?

Answer 1

Something like this should be pretty close. I removed the formatting of dates since that really belongs in the front end. Also, you do not want to use string literals as column aliases. It is confusing and was deprecated in sql server 2016. https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver15

select *
from 
(
    SELECT 
        [e].[BusinessEntityID]
        , [e].[JobTitle]
        , CONCAT (p.FirstName, ' ', p.LastName) AS Full_Name
        , dep.Name AS Department
        , Start_Date = isnull(edh.StartDate, getdate())
        , End_Date = isnull(edh.EndDate, getdate())
        , IIF(LEFT(pp.PhoneNumber, 3) = '1 (', ' ', LEFT(pp.PhoneNumber, 3)) AS Area_Code
        , eph.Rate
        , CASE CONVERT(CHAR(10), eph.PayFrequency)
            WHEN 2 THEN 'Biweekly'
            ELSE 'Weekly'
          END AS PayFrequency
        , IIF(e.SalariedFlag = 1, 'Y', 'N')                 AS SalaryEmployee
        , CASE 
            WHEN e.SalariedFlag = 1
            THEN ROUND((eph.Rate*40)*52, 2)
            ELSE NULL
          END                                               AS YearlySalary
        , RowNum = ROW_NUMBER()over(partition by e.BusinessEntityID order by edh.StartDate desc) --or whatever column makes sense here
    --INTO EmpInfo
    FROM [HumanResources].[Employee]    AS e
    INNER JOIN  [HumanResources].[EmployeeDepartmentHistory] AS edh
        ON edh.BusinessEntityID = e.BusinessEntityID
    INNER JOIN [Person].[Person] AS p 
        ON p.BusinessEntityId = e.BusinessEntityID
    INNER JOIN [Person].[PersonPhone] AS pp
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN [HumanResources].[Department] AS dep
        ON dep.DepartmentID = edh.DepartmentID
    INNER JOIN [HumanResources].[EmployeePayHistory] AS eph
        ON eph.BusinessEntityID = e.BusinessEntityID
) x
where x.RowNum = 1
ORDER BY x.BusinessEntityID;