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: 12/11/18 3:37 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?

Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our public forums to post questions.

Navigation

Social Media

Powered by 10MinutesWeb.com