Answer 1)
Similar Question(s):
Q) 1st and 2nd Maximum salary through same SQL Query There are so many ways to achieve this, but below i'm mentioning simple and best performing query
In Oracle: SELECT sal FROM(SELECT DISTINCT(sal) FROM employees ORDER BY sal DESC) WHERE ROWNUM<=2;
In SQL Server: SELECT TOP 2 sal FROM(SELECT DISTINCT(sal) FROM employees ORDER BY sal DESC) If they ask to write without using top use below queries:
Another solution Using UNION SELECT MAX(sal) FROM employees UNION SELECT MAX(sal) FROM employees WHERE sal < (SELECT MAX(sal) FROM employees)
Another solution Using DENSE_RANK SELECT DISTINCT(sal) FROM (SELECT sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS SAL_RANK FROM employees) empT WHERE empT.SAL_RANK <= 2 ORDER BY sal DESC
If the insist to write only correlated subquery
SELECT e.sal FROM employees e
WHERE 1 >= (SELECT count(*) FROM employess s
WHERE s.sal>e.sal)
|