How to Retrieve nth maximum salary from the salary table

[Gaurav arora]

Hi I m Having a Problem in retrieving records.
I want to get nth maximum value in a column.
I have tried it using top
select top n ColumnName from tablename where(select top n-1 ColumnName from tablename order by columnName desc)

But i m getting all the n records.
Any idea plz

[cutepinkbunnies]

I have a feeling you're going to have to use a cursor to get that kind of result. Read up on them, it should be pretty easy, but keep in mind they are a bit slow if you're using a lot of data.

[sureshmdb]

Hi,
select salary from tablename t1,tablenamet2
where n-1=(select count(*) from tablename where t1.sal > t2.sal)

Hi I m Having a Problem in retrieving records.
I want to get nth maximum value in a column.
I have tried it using top
select top n ColumnName from tablename where(select top n-1 ColumnName from tablename order by columnName desc)

But i m getting all the n records.
Any idea plz

[rathnakar]

hi friend,
this query selects 4th max record from the table .

select top 1 * from vehicles where sno in(select top 4 sno from vehicles )order by sno desc

regards,
rathnakar

[Ramesh S]

Try this

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

[mnagaraj]

Create table Employee

(

      Eid INT,

      [Name] varchar (10),

      Salary money

)

Go

 

Insert into Employee values (1,'harry',3500)

Insert into Employee values (2,'jack',2500)

Insert into Employee values (3,'john',2500)

Insert into Employee values (4,'xavier',5500) 

Insert into Employee values (5,'steven',7500) 

Insert into Employee values (6,'susana',2400) 

Go

 

Select * From Employee Order by Salary DESC

 

DECLARE @N INT

SELECT @N = 1

Select * From Employee E1 Where

    (@N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where

            E2.Salary > E1.Salary)

 

Drop table Employee

[apegram]

Here is an example using an order table. You take the top 1 from the top N in reverse order.

select top 1 OrderTotal
From
(select top 10 OrderTotal from orders order by ordertotal desc) x
Order By x.OrderTotal

Let's say my top 10 OrderTotals (in descending order) are

1000
990
950
900
875
800
750
725
675
650

That would be the result of the statement

SELECT top 10 OrderTotal FROM orders ORDER BY ordertotal DESC

From there, we select the top 1, but now we order it by OrderTotal ascending instead of descending. We also alias the results of the From clause as x.

Running that, the result of the full query will be 650, which is the 10th (N, in our example) highest order total in our table.

Edit: Ah, I see this is an old thread resurrected earlier today. Well, I hope this helps someone, although I'm sure the original author has long sinced found a solution.

[NETProgrammer]

Use the code below.

select *  from Employee order by salary desc

[crishlay]

select top 1 max(salary) from employee