There are some questions which never gets old and I keep on seeing them again and again every time I participate in the interview to help hire qualified Developers and DBA. Here is another popular question which I often see.
Question: Write a query to retrieve Second (or Nth) highest salary of an employee from a table.
Answer: Here is the generic query for the same. You can use the following query for any other similar logic.
Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.
FROM Employee E1
WHERE (N-1) = (
FROM Employee E2
WHERE E2.Salary > E1.Salary)
In the above example, the inner query uses a value of the outer query in its filter condition, meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.
Reference : Pinal Dave (https://blog.sqlauthority.com)