rowset indicator

• Benjamin Kovac

  Right there with Babe

  Points: 718

  More actions

  July 19, 2007 at 6:03 pm

  #173853

  Hi everybody,

  Just wondering if there is any keyword,function in T-SQL which I can use to filter out an n-positioned rowset,similar to the TOP keyword which filters out the first n rowsets?

  I am in the situation, when there is a table with ID-primary key,MyName and Salary columns and I want to get the person(MyName) whos salary(Salary) is the second,third or n-d highest from all in that table.I tried this with setting up a int parameter @top=n and use it to get the TOP n columns ordered by the Salary in DESC, then to use another TOP 1 of that result in ASC,like below:

  CREATE PROC salary_n

  @top int

  AS

  SELECT TOP 1 Table2.ID,Table2.MyName FROM

  (SELECT TOP (@top) * FROM MyTable ORDER BY Salary DESC) AS Table2

  ORDER BY Table2.Salary ASC

  Thank you in advance

  Ben

• Benjamin Kovac

  Right there with Babe

  Points: 718

  More actions

  July 20, 2007 at 4:54 pm

  #721299

  As I see almost noone had a chance to take a look..

  Played a bit with discussion forums msdn etc. and got it in a couple of minutes.

  There is a CURSOR to do that job I am looking for.

  So declare a cursor with the select statement with ORDER BY Salary DESC,open the cursor and FETCH the ABSOLUTE n row to get the n-d highest salary.

  If there is any concerns please let me know.

  PS:I am still a newbie so sorry for too easy questions

  Cheers

  ben

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  July 23, 2007 at 5:19 am

  #721488

  You need not need a cursor to do this for you in sql 2005. You have row_number() function in sql 2005 to do this.

  Read more on that in link http://www.sql-server-performance.com/ak_ranking_functions.asp

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com