In terms of the performance-boosting, if I want to get the employee with the highest salary in the company I can get that by one of the two following techniques.

• selecting the employee and applying the order by clause and then limiting the result to 1

select employee from employees order by salary desc limit 1; 

• Or I can achieve that by using rank() window function and get the lowest rank.

select employee from (
       select employee, rank() over (order by salary desc) rnk from employees)
where rnk =1 

I see the first solution is optimum as I apply the sorting algorithm once and get the answer. Instead of selecting from sorted temp data.

I need the experts' opinions.

CodePudding user response：

SQL, as a declarative language, is about the "What", not the "How".
You declare what you want to get as a result and it is up to the database to decide how to do it depends on your code as well as the specific SQL engine, the system configuration, the database configuration, the data demographics, the collected statistics and more.
In some SQL engines you might hint the database as to your preferred execution plan, however, this hint might cause an execution error or be ignored. I have even seen scenarios where the execution plan did not reflect some optimizations done by the database.

In other words -
Your assumption as to how a database execute your 2 queries might be completely wrong.
There is no reason to assume that the 2nd query will store temp results or even that any of the queries will use sorting at all.

And last -
What you are trying to achieve can actually be done without any sorting with complexity of o(n).