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

Recommended Answers

All 8 Replies

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.

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

commented: bumping old threads. Please read the forum rules -1

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

commented: bumping old threads. Please read the forum rules -1

Try this

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
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

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.

Use the code below.

select *  from Employee order by salary desc
commented: Wrong answer:- Read question properly before posting any answer. -3

select top 1 max(salary) from employee

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.