Getting a selection from multiple tables using JOIN-CodePudding

There is a task: Display the hourly rates of employees, indicating the maximum rate for each department in the column Max In Department. Within each department, divide all bets into groups so that bets with the same values are part of the same group.

I wrote a request to get a breakdown of bids into groups in departments, and I ran into the problem that the maximum bid in the department is calculated by the column with the breakdown of the bid per group, but it is necessary by departments, I tried both through a separate request and a connection with this and through a nested query, I can't understand what I'm doing wrong

I will be grateful for any help!

SELECT Name AS DepartmentName, Rate AS GroupOfRate, MAX(eph.Rate) AS MaxRateInDepartment
FROM HumanResources.EmployeeDepartmentHistory edh
INNER JOIN HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID
INNER JOIN HumanResources.EmployeePayHistory eph
ON eph.BusinessEntityID = edh.BusinessEntityID
GROUP BY Name, Rate

The first photo is the result of my request with an error. The second photo, the tables on which the selection is made (1 table EmployeePayHistory, where the employee rates are located; 2 table EmployeeDepartmentHistory; 3 table just take the names of departments). The third photo, as it should turn out (made in Paint)

Photo #1 Photo #2 Photo #3

CodePudding user response：

One possible way is to add a partition.

  SELECT Name AS DepartmentName
       , Rate AS GroupOfRate
       , MAX(eph.Rate) OVER ( PARTITION BY Name ORDER BY Name ) AS MaxRateInDepartment
    FROM HumanResources.EmployeeDepartmentHistory edh
         INNER JOIN HumanResources.Department d
                 ON edh.DepartmentID = d.DepartmentID
         INNER JOIN HumanResources.EmployeePayHistory eph
                 ON edh.BusinessEntityID = eph.BusinessEntityID
GROUP BY Name
       , Rate

By grouping by rate, you're restricting your MAX to Department Name and Rate. The OVER clause frees it up to look only at the department name.

You can confirm by adding an ORDER BY clause to the query.

ORDER BY Name
       , Rate

This should show that the first row for each department matches the department maximum.

CodePudding user response：

Your GROUP BY Name, Rate clause will create separate groups for each distinct department name and rate, which means every rate within that group will be the same. That is why Max(Rate) is giving you the same value as Rate.

If you need to display individual rates and "max rate per department" on the same line, you can use a window function. SQL window functions can be thought of as defining their own scope separate from GROUP BY. In your case you want to drop the GROUP BY and calculate the MAX rate partitioned by department.

Search for "SQL window function" for more information, syntax, and examples.