0

Write SQL query to get the second highest salary

select max(Salary) as SecondHighestSalary 
from  Employee 
where Salary<(select max(Salary) from Employee);

I would like to ask why cannot compare directly Salary < max(Salary) at the where in SQL?

Tran Tran
  • 39
  • 6
  • 1
    It should work. are you getting any error? – mkRabbani Apr 18 '20 at 07:05
  • Thank @mkRabbani for the reply. It works but I did not understand why I Cant write: ```select max(Salary) as SecondHighestSalary from Employee where Salary – Tran Tran Apr 18 '20 at 07:11
  • 1
    'why cannot compare directly Salary < max(Salary) ' - That's just the way sql works , you have to declare what you want – P.Salmon Apr 18 '20 at 07:11
  • Note that this isn't scalable; you cannot (easily) extend this method to grab the third highest salary – Strawberry Apr 18 '20 at 07:15
  • 1
    You must to know Order of Execution in Sql: (1) from clause is chose table --> (2) where clause is filter data --> select data. At the step 2 we do not have any data is select, it lead to Salary data is not define, that the reason you can't write query like that. More info: https://www.sisense.com/blog/sql-query-order-of-operations/ – Phong Nguyễn Apr 18 '20 at 07:25
  • this link should help- https://stackoverflow.com/questions/42470849/why-are-aggregate-functions-not-allowed-in-where-clause/42471383 – mkRabbani Apr 18 '20 at 07:45
  • Thanks @PhongNguyễn for your explanation. You save my day, Man! – Tran Tran Apr 18 '20 at 07:47
  • don't forget to upvote my comment if useful, have nice day man – Phong Nguyễn Apr 18 '20 at 07:52
  • *why cannot compare directly Salary < max(Salary) at the where in SQL?* The condition removes some records from output. This changes (decreases) maximal value. This causes more records remove, which cause ... until "no rows found". Do you need in such result really? – Akina Apr 18 '20 at 09:00
  • @TranTran . . . This is how SQL is defined. You are free to invent and implement your own query language, but SQL does not allow aggregated and unaggregated references to the same column for the same table reference. – Gordon Linoff Apr 18 '20 at 12:46

0 Answers0