On a single query, how we get first, third and fifth highest value from a table in sql.

Question

3.67/5 (3 votes)

See more:

SQL

Only using a single query, how we get first, third and fifth highest salary together from a 'salary_table'.

Posted 9-Mar-15 2:24am

Deepak Tiwari (D'pak)

Add a Solution

Comments

Herman<T>.Instance 9-Mar-15 8:28am

MS SQL server, MySQl, Oracle, SAP/Sybase?

Deepak Tiwari (D'pak) 9-Mar-15 8:48am

MS SQL Server

4 solutions

Solution 2

Please follow below links..

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/[^]

sql query for nth highest & lowest salary with employee name[^]

I hope it will be helpful for you..

AARIF SHAIKH

Posted 9-Mar-15 3:00am

aarif moh shaikh

Solution 4

SELECT TOP 5 Salary FROM salary_table ORDER BY Salary DESC

Ignore returned records #2 and #4

Posted 9-Mar-15 3:17am

manchanx

Updated 9-Mar-15 3:28am

v2

Comments

Deepu S Nair 9-Mar-15 9:20am

?

[no name] 9-Mar-15 9:22am

Please read the question, look at my solution and vote up instead of down :-)

Deepu S Nair 9-Mar-15 9:24am

what you mean by "Ignore results 2 and 4"?

[no name] 9-Mar-15 9:27am

He's asking for the first, third and fifth highest salary. My query returns first, second, third, fourth and fifth highest. So he would just have to ignore (= skip reading) the second and fourth result in order to get just the first, third and fifth.

Deepu S Nair 9-Mar-15 9:31am

Skip reading?Before arguing just check solution 3.

[no name] 9-Mar-15 9:39am

What's the problem? I'm not saying my solution is the only valid one. It's one solution and it's a simple one. If you query something, you have somewhere some code that processes the results. In that code you can just skip/ignore the returned records #2 and #4 from my query result and you're done.

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)

Herman<T>.Instance · Accepted Answer · 2015-03-09T03:03:00

Solution 3

easier solution without CTE:

SQL

SELECT Name, Salary FROM
(
   SELECT ROW_NUMBER() OVER (order by salary desc) as rowNr, name, salary
   From tblSalary
) as r
where r.rowNr in (1,3,5)

Posted 9-Mar-15 3:03am

Herman<T>.Instance

Comments

Rajesh waran 9-Mar-15 9:27am

5*

Deepak Tiwari (D'pak) 9-Mar-15 9:27am

working fine............thanks

Maddy selva 9-Mar-15 9:31am

my 5+

W∴ Balboos, GHB 9-Mar-15 10:08am

Nice. Clean. => Elegent => +5

Rajesh waran · Accepted Answer · 2015-03-09T02:35:00

Solution 1

Try like this If you are using Sql Server 2008 R2 , It'll work,
And i am considering table name as Table1 and sample column name as Name,Column2,Column3.

SQL

with cte as (select a.Name,a.Column2,a.Column3,MAX(a.Salary) as Max_Salary,ROW_NUMBER() over(order by a.Name) as Rn  from Table  as a group by a.Name,a.Column2,a.Column3 ) select * from cte where Rn in (1,3,5)

Checked and it works,
Hope it will be helpful to you.

Posted 9-Mar-15 2:35am

Rajesh waran

Updated 9-Mar-15 2:45am

v4

Comments

Herman<T>.Instance 9-Mar-15 9:01am

why order by a.Name. The key is the Salary the name will show up after that.

Rajesh waran 9-Mar-15 9:26am

Yes you are right. And your solution looking Good.