WinSQL » Knowledge base

Document information

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

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?

Your Name

Email

Hide my email address

Captcha

Your Tip

Submit Tip