How to get second Highest Salary using Sql server

Question

1.00/5 (4 votes)

See more:

SQL

server

Hi,

How to get second highest salary with out using two select statements please give me the reply

Thank's

Posted 23-May-14 22:51pm

Member 10629036

Updated 10-Dec-17 19:49pm

v3

Add a Solution

Comments

Member 10629036 26-May-14 6:05am

With out using Two select statements i want to know the query for second highest salary.

[no name] 26-May-14 6:42am

Why?

Mandip Grewal 27-May-14 5:18am

Please check solution number 8 to retrieve second highest salary with one SELECT statement

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-05-23T23:03:00

Solution 3

Try:

SQL

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.Salary DESC)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 2

Posted 23-May-14 23:03pm

OriginalGriff

Comments

Mandip Grewal 27-May-14 4:44am

If there are two employees who have highest salary then this query will fetch the highest salary of second employee... You have to use DENSE_RANK() function instead of ROW_NUMBER()..

Mandip Grewal · Answer 2 · 2014-05-26T22:46:00

You can retrieve the second highest salary using following two queries..

In first query we have used CTE and DENSE_RANK to retrieve the second highest salary

SQL

WITH myTableWithRows AS (
SELECT (DENSE_RANK() OVER (ORDER BY myTable.Salary DESC)) as row,*
FROM myTable)

SELECT * FROM myTableWithRows WHERE row = 2

In second query, SELF JOIN is used to retrieve the second highest salary and also haven't used two SELECT statements.

SQL

SELECT top 1 t2.Salary 
FROM myTable t1, myTable t2
WHERE t1.Salary > t2.Salary 
ORDER BY t1.Salary DESC, t2.Salary DESC

DamithSL · Answer 3 · 2014-05-23T23:01:00

Solution 2

SQL

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

or

SQL

select MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee )

Posted 23-May-14 23:01pm

DamithSL

**Nirav Prabtani** · Answer 4 · 2014-05-23T23:06:00

Solution 4

try this.. :)

SQL

select top 1 salary from tableName where salary < (select max(salary) from tableName) order by salary desc

Posted 23-May-14 23:06pm

Nirav Prabtani

SRINU.TIRRI · Answer 5 · 2014-05-26T20:15:00

Solution 6

SQL

select max(salary) from emp where salary<(select max(salary) from emp)

Once try it Above Query
By
Srinu.T

Posted 26-May-14 20:15pm

SRINU.TIRRI

Updated 26-May-14 21:19pm

v2

Sergey Vaselenko · Answer 6 · 2014-05-26T21:33:00

Solution 7

SQL

SELECT
    TOP 1 *
FROM
    (
    SELECT
        TOP 2 *
    FROM
        Employee t
    ORDER BY
        t.Salary DESC
    ) t
ORDER BY
    t.Salary ASC

Posted 26-May-14 21:33pm

Sergey Vaselenko