Member Avatar for 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

  • 9 Contributors
  • 8 Replies
  • 257 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by crishlay

Dani AI

Generated

This thread shows the usual patterns (nested TOP, correlated COUNT and even a cursor), but the key thing to clarify first is what "nth maximum" means: do you want the nth distinct salary (skip ties) or the nth row when ordering by salary (count ties separately)? The choice determines the best solution.

A clear, efficient modern approach (SQL Server 2005+) uses window functions. To get the nth distinct salary (ties share the same rank) use DENSE_RANK:

WITH Ranked AS (
  SELECT Salary,
         DENSE_RANK() OVER (ORDER BY Salary DESC) AS rk
  FROM Employee
  WHERE Salary IS NOT NULL
)
SELECT Salary
FROM Ranked
WHERE rk = @N;

If the goal is "nth row" including duplicates, ROW_NUMBER is appropriate — but include a deterministic tie-breaker (for repeatable results):

WITH RN AS (
  SELECT Salary,
         ROW_NUMBER() OVER (ORDER BY Salary DESC, Eid) AS rn
  FROM Employee
)
SELECT Salary
FROM RN
WHERE rn = @N;

For SQL Server 2012+ another compact option is OFFSET/FETCH; combined with DISTINCT it can return the nth distinct salary:

SELECT DISTINCT Salary
FROM Employee
WHERE Salary IS NOT NULL
ORDER BY Salary DESC
OFFSET (@N-1) ROWS FETCH NEXT 1 ROW ONLY;

Notes and tips: and showed the COUNT(DISTINCT) correlated approach — correct but O(n^2) and slow on large tables. and used nested TOP — works in older versions but is harder to read and can be awkward with ties. ’s cursor suggestion is usually unnecessary and slow. Add an index on Salary, exclude NULLs if undesired, and if the requirement is to return full employee rows, rank within a CTE and then select * where the rank = @N.

Recommended Answers

All 8 Replies

Member Avatar for 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.

Member Avatar for 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

commented: bumping old threads. Please read the forum rules -1
Member Avatar for 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

commented: bumping old threads. Please read the forum rules -1
Member Avatar for Ramesh S

Try this

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
Member Avatar for 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
Edited by Ezzaral because: Added code tags. Please use them to format all code that you post.
Member Avatar for 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.

Edited by apegram because: n/a
Member Avatar for NETProgrammer

Use the code below.

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

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.