Click here to Skip to main content
15,879,095 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to find 3rd maximum salary when table contains duuplicate data.
Posted

Hi Mukesh ,

Please find the below query for 3 rd highest mark from table.


SQL
SELECT
    TOP 1 mark
FROM
    (SELECT DISTINCT TOP 3 mark FROM mark ORDER BY mark DESC) a ORDER BY mark
 
Share this answer
 
Comments
Sarabjot Singh Makkar 20-Feb-13 1:56am    
Hi Arul,

Your query gives the desired but can you please explain that why you use 'a' with second order by clause

Thanks in advance
Arul R Ece 20-Feb-13 2:10am    
Here 'a' is alias name for sub query
below query will give you more correct answer:-

SQL
select * from (
select dense_rank() over (order by sal desc) as num, * from tablename) a
where num=3
 
Share this answer
 
Use 2 queries

This gets you the top 3 salaries!
SQL
Select top 3 * from Table order by salary


Wrap that in a second query

SQL
Select top 1 *
From (Select top 3 * from Table order by salary)
order by salary desc


There is a more complex solution using Row_Number but the above will do the job.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900