|
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)
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.
| 
