Solved

# pls provide query to find 3 rd lowest sal  in sqlserver2005

Posted on 2011-04-28
242 Views
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
Question by:mu_ravi1

LVL 23

Expert Comment

``````SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) Serial, *
FROM Employee
) A
WHERE Serial = 3
``````
0

LVL 6

Accepted Solution

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

``````SELECT TOP 1 Name,Salary(SELECT TOP 3 Name,Salary FROM Employee
ORDER BY Salary) a
order by Salary DESC
``````
0

LVL 23

Expert Comment

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
``````

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

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
``````

Raj
0

Author Closing Comment

good
0

## Featured Post

### Suggested Solutions

How to format datetime as mm/dd/yyyy 6 56
Group by correlation 4 41
Modifying SQL 2008/2012 PARTITIONS 3 39
SQL Query 2 45
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…