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

PSUNIL

• Aug 7th, 2012

 

USING DENSE RANK U CAN GET IT

Code
SELECT SALARY FROM


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


WHERE DENSE_RANK=2;

purvi

• Aug 7th, 2012

 

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

NagaRaja

• Aug 17th, 2012

 

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

Priyasagi

 Profile Answers by Priyasagi 

• Aug 29th, 2013

 

Code
SELECT ename,SALARY FROM


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


WHERE DENSE_RANK=2


/

Saktipada Upadhyaya

• Mar 24th, 2014

 

Code
SELECT *


FROM Emp


WHERE Sal = (


                      SELECT MAX(Sal)


                      FROM Emp


                      WHERE LEVEL = 2


                       CONNECT BY PRIOR Sal >= Sal


                        GROUP BY LEVEL


                    );

Navneet Verma

 Profile Answers by Navneet Verma 

• Mar 27th, 2014

 

Select max(Sal) from Emp Where Sal not in(Select max(Sal) from Emp)

jyotirmaya mohanty

• Apr 11th, 2014

 

Fetching Nth Highest Salary:

Code
SELECT * FROM Products P1


WHERE (N-1) = (


SELECT COUNT(P2.Price)


FROM Products P2


WHERE P2.Price > P1.Price);

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

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
SELECT max(Emp_NetSalary) FROM Emp_Information WHERE Emp_NetSalary NOT IN(SELECT max(Emp_NetSalary) FROM Emp_INformation)


 

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);

Mohammed Daginawala

• Apr 3rd, 2015

 

Code
SELECT MIN (SAL) 


FROM EMP 


WHERE SAL IN (


SELECT TOP 2 SAL 


FROM EMP


ORDER BY SAL)

Nivetha Thinakaran

 Profile Answers by Nivetha Thinakaran 

• Apr 20th, 2015

 

Code
 


SELECT MAX(salary) FROM employees


  WHERE salary<>(SELECT MAX(salary) FROM employees)

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

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

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

chandra sekhar

• May 21st, 2015

 

This Query will give 2nd highest sal

Code
SELECT *


FROM EMP Emp1


WHERE (2-1) = (


               SELECT COUNT(DISTINCT(Emp2.Sal))


               FROM EMP Emp2


               WHERE Emp2.Sal > Emp1.Sal


               );

Binod

• May 24th, 2015

 

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

jeevan

• Jun 17th, 2016

 

Here it is

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


INTO #t1 


FROM Emp;


SELECT * FROM #t1 WHERE Salrank = 2;

Ade

• Jun 23rd, 2016

 

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


ORDER BY salary DESC) AS temp


ORDER BY salary

Prashant Sharma

• Aug 16th, 2016

 

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

Akshay

• Oct 2nd, 2016

 

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

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

Vivek

• Dec 7th, 2016

 

Using correlated Query we can solve the same

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

Sol

• Apr 25th, 2017

 

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

Mukesh

• May 16th, 2017

 

Code
SELECT max(salary)


FROM emptable


WHERE salary < (SELECT max(salary)


                FROM emptable);

Ajinkya

• Jul 6th, 2017

 

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

Anupama

• Jul 12th, 2017

 

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

Zayeem

• Aug 5th, 2017

 

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

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

nagendra

• Nov 11th, 2017

 

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

Santhosh Kumar Gujja

• Nov 27th, 2017

 

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

Pankaj Yadav

• Feb 23rd, 2018

 

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