Senator Guerra Souty original series calendar,replica hublot blue steel peach pointer collocation of rolex replica Rome digital scale, track type minute replica watches scale shows that the classical model is swiss replica watches incomparable, wearing elegant dress highlights.
mr-ponna.com

 

YOU ARE HERE: HOME Questions Write a query to get max salary and second max salary in order in one query



Query to get max salary and second max salary in order in one query

View(s): 26095

Write a query to get max salary and second max salary in order in one query?

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)

  Asked in:  ValueLabs   Expertise Level:  Intermediate
  Last updated on Friday, 03 January 2014
4/5 stars (16 vote(s))

Register Login Ask Us Write to Us Help