How to retrieve a second highest salary from a table?

How to retrieve a second highest salary from a table?
Note:Suppose salaries are in duplicate values
eg:
Name Sal
Malli 60000
Pandi 60000
Rudra 45000
Ravi 45000

Questions by BJMallinatha

Showing Answers 1 - 29 of 29 Answers

anup

  • Jun 22nd, 2012
 

you can specify a sql query in source qualifier like
Select name,sal from Employee order by sal---it is used to sort the salary column so that the max salary will be in the starting
still we have one more issue
If two or more persons have the highest salary then the second row wont be the second highest salary so inorder to get the 2nd highest salary
-- Take an expression transformation
-- pass the sal as input port--I_sal (input + output port)
-- create a variable port-- v_sal which has the value of I_sal

-- Create another variable port v_logic which has the expression as IIF(v_sal=v_prevsal,NO,secondHighsal)
which returns No if the current sal and previous salary are equal if not it will return secondHighsal
--create one more output port -- v_result as string and give the value of v_logic to it
--create a variable port v_prevsal
make sure that this port should be created after the variable port in which we gave the expression
because it should retain the previous value when it is being used.

-- Next create a filter transformation and pass the I_sal and v_Result ports to that and check the V_result for the
seconHighSal value and pass the respective sal to target

Hope you will get the solution

  Was this answer useful?  Yes

PSUNIL

  • Aug 7th, 2012
 

USING DENSE RANK U CAN GET IT

Code
  1. SELECT SALARY FROM

  2. (SELECT SALARY ,DENSE_RANK() OVER(ORDER BY SALARY) AS DENSE_RANK FROM EMP)

  3. WHERE DENSE_RANK=2;

  Was this answer useful?  Yes

purvi

  • Aug 7th, 2012
 

select max(salary) from EmpTable where salary not in (select max(salary) from EmpTable));

  Was this answer useful?  Yes

NagaRaja

  • Aug 17th, 2012
 

Select Max(Salary) from Emp Where Salary < (Select Max(Salary) from Emp)

  Was this answer useful?  Yes

Priyasagi

  • Aug 29th, 2013
 

Code
  1. SELECT ename,SALARY FROM

  2. (SELECT ename,sal SALARY ,DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSE_RANK FROM EMP)

  3. WHERE DENSE_RANK=2

  4. /

  Was this answer useful?  Yes

Saktipada Upadhyaya

  • Mar 24th, 2014
 

Code
  1. SELECT *

  2. FROM Emp

  3. WHERE Sal = (

  4.                       SELECT MAX(Sal)

  5.                       FROM Emp

  6.                       WHERE LEVEL = 2

  7.                        CONNECT BY PRIOR Sal >= Sal

  8.                         GROUP BY LEVEL

  9.                     );



  Was this answer useful?  Yes

jyotirmaya mohanty

  • Apr 11th, 2014
 

Fetching Nth Highest Salary:

Code
  1. SELECT * FROM Products P1

  2. WHERE (N-1) = (

  3. SELECT COUNT(P2.Price)

  4. FROM Products P2

  5. WHERE P2.Price > P1.Price);



  Was this answer useful?  Yes

StalinK

  • Sep 22nd, 2014
 

Table Name: Employee
Column name :Employee_id,First_name,Last_name,Salary

Select Employee_id,First_name,Last_name,Salary From
(
Select *,RANK() OVER ( Order by salary desc) as Test From employee
) A
Where Test=3

  Was this answer useful?  Yes

Qazi Ahmad

  • Dec 13th, 2014
 

Find the Salary from Emp_Information table

Column name is Emp_NetSalary

Emp_NetSalary
2000
4000
6000
8000
your correct ans is 6000

Code
  1. SELECT max(Emp_NetSalary) FROM Emp_Information WHERE Emp_NetSalary NOT IN(SELECT max(Emp_NetSalary) FROM Emp_INformation)

  2.  

  Was this answer useful?  Yes

Indhumathi

  • Mar 5th, 2015
 

SELECT SAL
FROM EMP X,Y
WHERE 2=(SELECT COUNT(DISTINCT Y.SAL)
FROM EMP Y
WHERE X.SAL>=Y.SAL);

  Was this answer useful?  Yes

Mohammed Daginawala

  • Apr 3rd, 2015
 

Code
  1. SELECT MIN (SAL)

  2. FROM EMP

  3. WHERE SAL IN (

  4. SELECT TOP 2 SAL

  5. FROM EMP

  6. ORDER BY SAL)

  Was this answer useful?  Yes

Ayushi

  • May 12th, 2015
 

Please find the below two ways to solve this question:
SELECT Top 1 *
FROM
(
SELECT TOP 2 * FROM Emp Order BY Salary Desc)tmp
ORDER By Salary
SELECT * FROM
(
SELECT *,RANK() OVER (Order By Salary Desc) As Ranking FROM Emp) tmp
WHERE Ranking = 2

  Was this answer useful?  Yes

Ayushi

  • May 12th, 2015
 

This Query Will not give you the correct result if data is redundant since you have used Rank.
SELECT * FROM
(
SELECT *,DENSE_RANK() OVER (Order By Salary Desc) As Ranking FROM Emp) tmp
WHERE Ranking = 2

  Was this answer useful?  Yes

Ayushi

  • May 12th, 2015
 

This query will not give you the correct result since you have not used DESC in order by clause. Therefore, inner query will sort the records in ASC order and will give you second lowest record. Also, you have to use DISTINCT in inner query otherwise the result will be incorrect if salary data is redundant.
SELECT MIN(Salary)
FROM
(
SELECT DISTINCT TOP 2 Salary FROM Emp Order BY Salary Desc)tmp

  Was this answer useful?  Yes

chandra sekhar

  • May 21st, 2015
 

This Query will give 2nd highest sal

Code
  1. SELECT *

  2. FROM EMP Emp1

  3. WHERE (2-1) = (

  4.                SELECT COUNT(DISTINCT(Emp2.Sal))

  5.                FROM EMP Emp2

  6.                WHERE Emp2.Sal > Emp1.Sal

  7.                );

Binod

  • May 24th, 2015
 

SELECT MAX(SAL) FROM EMP
WHERE SAL != (SELECT MAX(SAL) FROM EMP);

  Was this answer useful?  Yes

jeevan

  • Jun 17th, 2016
 

Here it is

Code
  1. SELECT *, DENSE_RANK() OVER(ORDER BY Sal DESC) AS Salrank

  2. INTO #t1

  3. FROM Emp;

  4. SELECT * FROM #t1 WHERE Salrank = 2;

  Was this answer useful?  Yes

Ade

  • Jun 23rd, 2016
 

Code
  1. SELECT Name, TOP 2 (salary) FROM (SELECT DISTICT (salary)

  2. ORDER BY salary DESC) AS temp

  3. ORDER BY salary

  Was this answer useful?  Yes

Prashant Sharma

  • Aug 16th, 2016
 

Code
  1. SELECT TOP 1 sal FROM (SELECT DISTINCT TOP 2 sal FROM table_name ORDER BY sal DESC) AS second_highest_salary ORDER BY sal

  Was this answer useful?  Yes

Akshay

  • Oct 2nd, 2016
 

Select Sal from emp order by sal desc limit 1,1;

  Was this answer useful?  Yes

Mike

  • Nov 4th, 2016
 

SELECT TOP 1 sal FROM (SELECT DISTINCT TOP 2 sal FROM table_name ORDER BY sal ASC) AS second_highest_salary ORDER BY sal

  Was this answer useful?  Yes

Vivek

  • Dec 7th, 2016
 

Using correlated Query we can solve the same

Code
  1. SELECT E1.salary FROM Employee E1 WHERE 1 = (SELECT COUNT(1) FROM Employee E2 WHERE E2.salary>E1.salary)

  Was this answer useful?  Yes

Sol

  • Apr 25th, 2017
 

SELECT DISTINCT salary FROM Employees ORDER BY salary DESC Limit 1 ,1

  Was this answer useful?  Yes

Mukesh

  • May 16th, 2017
 

Code
  1. SELECT max(salary)

  2. FROM emptable

  3. WHERE salary < (SELECT max(salary)

  4.                 FROM emptable);

  Was this answer useful?  Yes

Ajinkya

  • Jul 6th, 2017
 

Use dense_rank for generic answer. You can select any of salary based on max, min required by selecting rank.

  Was this answer useful?  Yes

Anupama

  • Jul 12th, 2017
 

Select Max(salary)
From table_name
Group by salary
Order by salary desc
Limit 1,1;

  Was this answer useful?  Yes

Zayeem

  • Aug 5th, 2017
 

SELECT MAX(salary)
from TABLE_NAME
WHERE
salary NOT IN (SELECT MAX(salary) from TABLE_NAME);

  Was this answer useful?  Yes

Iqbal Singh

  • Aug 7th, 2017
 

SELECT NTH_VALUE(salary,2)
OVER (ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "2nd Highest"
FROM employees;
You may use this one for any one

  Was this answer useful?  Yes

nagendra

  • Nov 11th, 2017
 

SELECT * from TableName order by Column Desc limit 1 offset 1 ; (Give value in n for n+1th highest value)

  Was this answer useful?  Yes

Santhosh Kumar Gujja

  • Nov 27th, 2017
 

SEL SAL FROM(
SEL DISTINCT SAL FROM TABLE
) O QUALIFY ROW_NUMBER() OVER(ORDER BY SAL DESC)=2

  Was this answer useful?  Yes

Pankaj Yadav

  • Feb 23rd, 2018
 

Code
  1. SELECT max(salary) FROM employee WHERE salary <> (SELECT max(salary) FROM employee)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions