select max, min row for each group without subquery with case of same [closed]

I’m trying to avoid sub queries because it generally takes longer to run. Came across this solution which works nicely but has a snag when there is another person with the same role with the same amount

For example, lets add another row

Steven 140,000

Is it possible to change the T-SQL code to pick either Steven or John as the top paying DevOps Engineer without using sub-queries? It doesn’t matter which one

https://www.eversql.com/select-max-min-last-row-for-each-group-in-sql-without-a-subquery/

Answer

Well here is one way to tackle the problem as long as you don’t care which Devops Engineer is the highest paid:

CREATE TABLE #employee(EmpName varchar(50), salary int,role varchar(30))

INSERT INTO #employee VALUES('David',130000,'Software Engineer')
INSERT INTO #employee VALUES('John',140000,'DevOps Engineer')
INSERT INTO #employee VALUES('Bob',120000,'Software Engineer')
INSERT INTO #employee VALUES('Sarah',130000,'DevOps Engineer')
INSERT INTO #employee VALUES('Sarah',130000,'DevOps Engineer')

INSERT INTO #employee VALUES('Alice',110000,'Software Engineer')
INSERT INTO #employee VALUES('Alice',110000,'Software Engineer')
INSERT INTO #employee VALUES('Steve',95000,'DevOps Engineer')
INSERT INTO #employee VALUES('Steve',95000,'DevOps Engineer')
INSERT INTO #employee VALUES('Steven',145000,'DevOps Engineer')


;WITH CTE
AS(
SELECT EmpName,Salary, Role, ROW_NUMBER() OVER(Partition by Role ORDER BY Salary desc) RN
FROM #employee)
SELECT * from CTE where RN = 1