CODEWITHSUNDEEP
sqloracle-databaseplsqlsubqueryquery-optimization
Subham Singh
Subham Singh

Reputation: 29

What is the best way(in case of performance) to find out the user details with maximum salary?

We have user details with a column of salary also, how can we print the user details with the maximum salary, I don't want to use the Subquery, and yeah how subquery will reduce the performance. I know this query is wrong but I want something like this:

  select User_name, user_id 
  from dual where salary=Max(salary);

Upvotes: 0

Views: 74

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

Analytic functions help.

Using a CTE (which is kind of a subquery; don't be afraid of it, it doesn't bite and won't affect performance), query might look like this (based on sample Scott's schema):

SQL> select ename, sal from emp order by sal desc;

ENAME             SAL
---------- ----------
KING             5000         --> this is the highest salary
FORD             3000         --> FORD and SCOTT share the 2nd place
SCOTT            3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250         --> WARD and MARTIN are then 9th
MARTIN           1250
ADAMS            1100
JAMES             950
SMITH             800

14 rows selected.

Query is then

SQL> with temp as
  2    (select ename,
  3            dense_rank() over (order by sal desc) rnk
  4     from emp
  5    )
  6  select ename
  7  from temp
  8  where rnk = 1;

ENAME
----------
KING

SQL>

Why dense_rank? Because two (or more) employees can have the same salary so they "rank" the same. For example, if you want to know whose salary is ranked as 9th, you'd

SQL> l8
  8* where rnk = 1
SQL> c/1/9
  8* where rnk = 9
SQL> /

ENAME
----------
WARD
MARTIN

SQL>

Query you suggested (although wrong, but - I got the idea) looks like this:

SQL> select ename
  2  from emp
  3  where sal = (select max(sal) from emp);

ENAME
----------
KING

SQL>

And yes, it affects performance because you're fetching data from the same emp table twice: once to find the max salary (in a subquery), and then in the main query to find who it belongs to.

Upvotes: 1

Related Questions