can anybody tell us, how to select 2nd max salary from
table.
my id is ashish.akk@gmail.com
Answers were Sorted based on User's Feedback
Answer / dileep.t
If you need the second highest salary only
then following will also work,
select Max(Salary) from TableName
where Salary not in(
Select Max(Salary) from TableName)
Is This Answer Correct ? | 20 Yes | 3 No |
Answer / preeti chauhan
CREATE TABLE T1(SALARY int, [NAME] varchar(50))
SELECT TOP 1 SALARY FROM (SELECT TOP 2 SALARY FROM T1 ORDER
BY SALARY DESC)T1 ORDER BY SALARY
Is This Answer Correct ? | 15 Yes | 3 No |
Answer / kumar_kisna
select max(salary) from table where salary<(select max
(salary) from table)
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / divya
select max(sal) from tablename where sal<(select
max(sal)from tablename)
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / arun kumar ks
In the case more than one highest SALARY, only the Dilip’s
answer is correct.
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / sabin v jacob
SELECT Min(Sal) FROM (SELECT TOP 2 Sal FROM emp ORDER BY
Sal desc) as Temp
or
SELECT Min(Sal) FROM emp WHERE Sal IN(SELECT TOP 2 Sal FROM
emp ORDER BY Sal DESC)
Is This Answer Correct ? | 5 Yes | 3 No |
Answer / rajendra
Select Top 1 * From Tablename where salary in
(select top 2 salary from tablename order by salary desc)
order by salary asc
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / neeru
I think Daleep answer is not so precise, if you want 3rd or
ny specified answer, this only can only be seems right for
2nd highest salary, else, its not optimise query.I found
Preeti's query more optimistic and accurate.
Is This Answer Correct ? | 1 Yes | 0 No |
In SQL Server 2000 Query:
-------------------------
select max(salary) from Employee where salary not in(select
max(salary) from employee)
This Query only selects the 2'nd Higehest Salary in that
table. So, you will try...
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / harikumar
select max(salary) as secondMax from employee where id <>
(select max(salary) from employee)
Is This Answer Correct ? | 1 Yes | 0 No |
Rate yourself in .NET and SQL ?
1 Answers Cognizant, HCL, SunGard,
What are relationships and mention different types of relationships in the dbms
What is difference between foreign key and unique key?
Does sql server 2016 have ssms?
How is sql server used?
what is the maximum size of a row? : Sql server database administration
Do you know what is difference between stored procedure and user defined function?
What is a trigger and its types?
What is the difference between cartesian product and cross join?
Explain the basic concepts of SQL server architecture?
2 Answers College School Exams Tests,
what is Data Transformation Services (DTS) ?can u explain me in detail?
What is mscorsvw.exe - process - microsoft .net framework ngen?