March 22, 2010 at 3:32 am
Hi,
I am having one employee table. I just wanted get the 3rd Max salary in the table.
Is there any global method to get the nth max?
Thanks for your help.
March 22, 2010 at 3:49 am
Hint: Windowing function Row_number() and order by clause. Do your research and let us know. Thanks 🙂
---------------------------------------------------------------------------------
March 22, 2010 at 3:52 am
You can go for rank function ,partitioning on the salary column. Then u can filter your column to get any of the n'th max salary based on your query.
March 22, 2010 at 4:06 am
I guess DENSE_Rank is the better way. I don't like to use Row_Number. Thanks for the answer.
Select Salary from Table1 Where Salrank='3' Where (Select Dense_Rank()
over (Order By Salary Desc) As SalRank from Table1)
Is it correct.
March 22, 2010 at 4:59 am
Yes, thanks. Dense_rank is a better way since there could be a tie and dense_rank handles it well.
untested (corrected) code (since you have not provided data to test)
Select * from (Select *,
Dense_Rank() over (Order By Salary Desc)
As SalRank from Table1) T1
where salRank = 3
;WITH CTE AS
(Select *,
Dense_Rank() over (Order By Salary Desc) As SalRank
from Table1
)
Select *
from CTE
where SalRank = 3
---------------------------------------------------------------------------------
March 22, 2010 at 5:02 am
chandrasekaran.ganapathy (3/22/2010)
I guess DENSE_Rank is the better way. I don't like to use Row_Number. Thanks for the answer.Select Salary from Table1 Where Salrank='3' Where (Select Dense_Rank()
over (Order By Salary Desc) As SalRank from Table1)
Is it correct.
DENSE_RANK and ROW_NUMBER are two different functions with different functionalities. Based on your needs you can use either of them. Can you explain as to why you don't like to use ROW_NUMBER?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2010 at 5:03 am
Thanks buddy...It is working.... Thanks a lot.
March 22, 2010 at 5:05 am
You're welcome, but apart from what I mentioned (as Kingston is also asking) do you have any reason why you do not want to use row_number ?
---------------------------------------------------------------------------------
March 22, 2010 at 5:08 am
No specific reason? I want to handle the tie value also. that's why? can we handle tie value also in row_number?
March 22, 2010 at 5:11 am
chandrasekaran.ganapathy (3/22/2010)
can we handle tie value also in row_number?
I dont think so.
---------------------------------------------------------------------------------
March 22, 2010 at 5:18 am
chandrasekaran.ganapathy (3/22/2010)
No specific reason? I want to handle the tie value also. that's why? can we handle tie value also in row_number?
ROW_NUMBER gives unique values even if it is a tie. That's why i said they have different functionalities. You can use DENSE_RANK if you want to handle a tie. But ROW_NUMBER is useful when you want unique Ordering. I felt you had some personal preferences with ROW_NUMBER 😀
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2010 at 5:19 am
Hi Kingston,
Just for curiosity asking, can we handle the tie value in Row_number()?
March 22, 2010 at 5:28 am
Nothing.
Thanks kingston.
March 22, 2010 at 5:30 am
chandrasekaran.ganapathy (3/22/2010)
Hi Kingston,Just for curiosity asking, can we handle the tie value in Row_number()?
No, you can't. You can use the RANK or the DENSE_RANK for the same.
Again both of these are different and their use depends on the way you want to order.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply