Nth Max /Min value

• GuruGPrasad

  SSCommitted

  Points: 1768

  More actions

  March 31, 2012 at 1:17 am

  #259339

  How to find the nth max or min value in a column

  select * from (select distinct ext_price,ROW_NUMBER() Over (Order by ext_price desc) RNO from Inventory.purchase_order_detail)as Test Where RNo=5

  Is there is any other simple way to find it?

  Regards

  Guru

• Thinknook

  SSCrazy

  Points: 2531

  More actions

  March 31, 2012 at 4:35 am

  #1467289

  Not sure if this would work or not, don't have a test instance handy at the moment.

  But you could possibly try using "HAVING" to filter in the inner query instead of the setup you currently have.

  Cheers

  />L

  -----------------
  ... Then again, I could be totally wrong! Check the answer.
  Check out posting guidelines here for faster more precise answers[/url].

  I believe in Codd
  ... and Thinknook is my Chamber of Understanding

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  March 31, 2012 at 11:30 am

  #1467317

  GuruGPrasad (3/31/2012)

  ---

  How to find the nth max or min value in a column

  select * from (select distinct ext_price,ROW_NUMBER() Over (Order by ext_price desc) RNO from Inventory.purchase_order_detail)as Test Where RNo=5

  Is there is any other simple way to find it?

  Regards

  Guru

  As a sidebar, the "DISTINCT" in your code won't work because of the ROW_NUMBER() which is also part of the DISTINCT. If you want to find the 5th highest unique price, you need to either add a PARTITION clause to the ROW_NUMBER (or whichever windowing function you end up using) or determine the unique values of the price ahead of time. If you want to finde the 5th highest price even when "ties" are present, then simply remove the DISTINCT. Either way, I believe DISTINCT is in the wrong place here.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)