One table with EmpSalary in Employee Table. I need to find the second largest Salary what is paid by the company.?
How to find the Second largest value(Salary) from a table.?
+6
A:
;WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY SortColumn DESC) AS RowNumber, *
FROM YourTable)
SELECT * FROM CTE WHERE RowNumber = 2
anivas
2010-10-18 10:46:08
wats CTE in this??
pvaju896
2010-10-18 11:06:46
Common Table Expression. You can use any name. CTE_SALARY for instance.
anivas
2010-10-18 11:09:09
Wats happening here.. wen we running it..??
pvaju896
2010-10-18 11:20:03
ROW_NUMBER() OVER (ORDER BY ...) Clause orders your data based on the order by specification and auto-numbers it in 'RowNumber' column. You are then picking the second row from it.
anivas
2010-10-18 11:25:56
This is returning the second row but that might not be the second largest salary if more than 1 person has the highest salary as per Askashm's comment on question
Craig
2010-10-24 11:22:06
+3
A:
Try this: this should give the second largest salary:
SELECT MAX(EmpSalary) FROM employee WHERE EmpSalary < (SELECT MAX(EmpSalary) FROM employee);
Anurag
2010-10-18 10:59:06
I'm assuming you are looking for a very simple answer and not needing CTEs
Anurag
2010-10-18 11:00:03
There are two queries in this one: the inner query (SELECT MAX(EmpSalary) FROM employee) returns the largest salary. The outer query has a where condition that returns the largest value in the table which is less than value returned by inner query. Since inner query returns largest salary, outer query returns second largest value. Hope I have explained it properly.
Anurag
2010-10-18 11:41:36
A:
select top(1) prodMrp from Product where not prodMrp = (select top(1) prodMrp from Product order by prodMrp DESC ) order by prodMrp DESC
pvaju896
2010-10-18 10:59:23