Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Nth Maximum from a Table... Expand / Collapse
Author
Message
Posted Saturday, September 29, 2007 11:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
Comments posted to this topic are about the item Find Nth Maximum from a Table...

Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #404608
Posted Sunday, October 21, 2007 11:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2007 7:24 AM
Points: 1, Visits: 6
Hi,
This query is absolutely woking fine ...but if two employees r aving the same salary then it does not show correct result.. actuly it should show both employees for that rank.

this can be found using this query...:-->

select * from @table
where salary in (select top 1 salary from @table
where salary in (select top 3 salary from @Table order
by salary desc
)
order by salary
)

Looks complicated but not so ...
Post #413259
Posted Friday, September 19, 2008 6:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
May be the old thread but I want to show you other approaches
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx




Madhivanan

Failing to plan is Planning to fail
Post #572478
Posted Monday, August 17, 2009 11:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 9:01 AM
Points: 30, Visits: 63
top 5th Salary of every department

SELECT * FROM (

SELECT EMP_ID, DEPT_ID, SALARY, NAME

,RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RANKID

FROM EMP) A WHERE RANKID = 5

Post #772429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse