|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to find the second highest pay rate at AdventureWorks Cycles.
-- SQL find second highest salary - SQL common table expression - CTE
-- SQL nested CTE-s - SQL group by - MAX aggregate function
USE AdventureWorks;
-- Pay history may have multiple entries for an employee - latest(MAX) is active
SELECT EmployeeID,
RateChangeDate,
Rate
FROM HumanResources.EmployeePayHistory
WHERE EmployeeID = 4
ORDER BY RateChangeDate
GO
/*
EmployeeID RateChangeDate Rate
4 1998-01-05 00:00:00.000 8.62
4 2000-07-01 00:00:00.000 23.72
4 2002-01-15 00:00:00.000 29.8462
*/
WITH cteLastSalaryChange
AS (SELECT EmployeeID,
ChangeDate = Max(RateChangeDate)
FROM HumanResources.EmployeePayHistory
GROUP BY EmployeeID),
-- SQL CTE nesting
cteLastSalary
AS (SELECT eph.EmployeeID,
Rate
FROM HumanResources.EmployeePayHistory eph
INNER JOIN cteLastSalaryChange lsc
ON lsc.EmployeeID = eph.EmployeeID
AND lsc.ChangeDate = eph.RateChangeDate)
-- SELECT * FROM cteLastSalary -- for debugging
-- SQL TOP function
SELECT TOP 1 SecondHighestPayRate=Rate
FROM (SELECT TOP 2 Rate
FROM cteLastSalary
ORDER BY Rate DESC) a
ORDER BY Rate ASC
GO
/* Result
SecondHighestPayRate
84.1346
*/
Related link: WITH common_table_expression (Transact-SQL) |
