?
Solved

pls provide query to find 3 rd lowest sal  in sqlserver2005

Posted on 2011-04-28
6
Medium Priority
?
246 Views
Last Modified: 2012-05-11
Hi
pls provide query to find 3 rd lowest sal  in sqlserver2005
Employee
Name          Salary

Nathan        5000
Ramesh      10000
Sekar          15000
Ram             3000
Kannan       6000

ravi
0
Comment
Question by:mu_ravi1
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35483364
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) Serial, *
FROM Employee
) A 
WHERE Serial = 3

Open in new window

0
 
LVL 6

Accepted Solution

by:
hyphenpipe earned 2000 total points
ID: 35483367
with salary as
(select name, salary, row_number() over (order by salary)
 from table_name)
select * from salary where row = 3
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35483369
SELECT TOP 1 Name,Salary(SELECT TOP 3 Name,Salary FROM Employee
ORDER BY Salary) a
order by Salary DESC

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35483379
To select only Name & Salary, modify the query like
SELECT Name, Salary FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) Serial, *
FROM Employee
) A 
WHERE Serial = 3

Open in new window


If you modify there WHERE condition, you can get lowest value of any position.
For 2nd lowest make it 2
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35483392
If you want based highest salary instead of lowest salary, remove DESC in ROW_NUMBER()
SELECT Name, Salary FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Salary) Serial, *
FROM #Employee
) A 
WHERE Serial = 3

Open in new window


Raj
0
 

Author Closing Comment

by:mu_ravi1
ID: 35727847
good
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question