WinSQL » Knowledge base

Document information

Document ID:811
Subject:Find the nth Maximum and Minimum Values in a Given Column
Creation date:7/29/09 4:42 PM
Last modified on:7/29/09 4:42 PM


Details


This tip shows how to find the Nth minimum or maximum value from a table. For example, if you have a table called ORDERS containing a field called COST, the following queries will return the 5th largest order and 4th smallest order.

-- Following query will return the 5th largest order
select *
FROM ORDERS a
where 5 = -- 5 represents the Nth value.
(select count(distinct(b.COST)) from ORDERS b
    where a.COST <= b.COST)

-- Following query will return the 4th smallest order
select *
FROM ORDERS a
where 4 = -- 4 represents the Nth value.
(select count(distinct(b.COST)) from ORDERS b
    where a.COST >= b.COST)




User comments

Posted by Rock@head on 9/4/12 3:37 AM

It has been an amazing code, it completely helped me


Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com