'why is my sub-query when ran independently is not giving output

I was trying to find the second highest salary but when I run the below query:

select EmpID, Empsal, dense_rank() over (order by Empsal desc) r
from Employee_1 ;

is throwing an error.

When I re-write the same in other way, it is working very fine.

select *
from
  (select EmpID, Empsal, dense_rank() over (order by Empsal desc) r
   from Employee_1)
where r = 2;

Can anyone help me in understanding this?

sqltsqldatabricks


Solution 1:[1]

The FROM and WHERE clauses are evaluated before the SELECT one. You can see this in the documentation, paragraph "Logical Processing Order of the SELECT statement".

In your first query, you assign the alias r in the SELECT, but you use it in the WHERE when it has not been already created, which throws an error (by the way, next time, please include WHAT error you are getting).

In the second query, the derived table (ie the parenthesis, the subquery) is part of the FROM clause, which is processed before the WHERE, so no error is thrown.

Solution 2:[2]

You can use offset and fetch next:

select EmpID, Empsal
from Employee_1
order by Empsal desc
offset 1 rows
fetch next 1 rows only;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 George Menoutis
Solution 2 Lajos Arpad

Related Questions