Retrieve attribute with max function-CodePudding

How retrieve other attribute with max function like max salary with name. to retrieve max salary along with their name please help anyone.

CodePudding user response：

If my understanding of the question is correct, you can use something like this:

select name, salary from table where salary = (select max(salary) from table)

CodePudding user response：

   With SalaryOrder AS (
     Select name, salary, Row_Number() Over(Order By salary Desc) RN
     From table
    )
    Select /*the top clause is needed as described below*/ Top (1) *
   From SalaryOrder Where RN = 1

this query may return more than a record when existing people with the same salary. To solve this you can add more orders in CTE or use Top one to pick a random record.

CodePudding user response：

RANK() should be an efficient way to get name where salary is MAX. Using sub-query will also get us the same result but it will take more time than RANK() in some cases.

Query:

SELECT name, salary
FROM
    (
     SELECT name, salary, RANK() over(ORDER BY salary DESC) AS rnk
     FROM your_table
    ) AS a
WHERE rnk=1

Look at the db<>fiddle with time consumption. (The time may vary for different runs)