|
|
| |
|
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
1) You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description. |
--**************************************
--
-- Name: nth largest/smallest from a tab
-- le
-- Description:To find Nth largest and S
-- mallest 'value'(Numbers) from a Table.
-- By: MuraliKrishna Vedagiri
--
-- Inputs:The N: The index of the larges
-- t/smallest number.
--
-- Returns:Largest / Smallest
--
-- Assumes:SImple SQL knowledge
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/xq/ASP/txtCod
-- eId.374/lngWId.5/qx/vb/scripts/ShowCode.
-- htm--for details.--**************************************
--
SELECT a.itemid FROM item a WHERE (n-1) = (select count(*) from item b where b.itemid > a.itemid)
/** BY changing > TO < you can get Smallest Number **
** IF you want TO find the 4th largest, THEN 4-1 =3 in place OF (n-1)/ | |
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
12/18/2001 6:19:50 AM:James Travis
This is great, works as promised.
|
12/18/2001 9:28:45 AM:kevin
why can't you just use...
SELECT TOP n
[PERCENT] a, b FROM myTable
|
12/18/2001 9:51:29 AM:Srinivas VM
Perfect and neat.
|
6/13/2002 7:29:28 AM:Sushil Daga
Wonderful. but will fail if there are
multiple records with the same value,
instead use:
SELECT a.itemid FROM
item a WHERE (n-1) = (SELECT
COUNT(distinct b.itemid) FROM item b
WHERE b.itemid > a.itemid)
if no
duplicate values then use this for
better performance:
SELECT a.itemid
FROM item a WHERE (n-1) = (SELECT
COUNT('x') FROM item b WHERE b.itemid >
a.itemid)
Please note: My solution
will work perfectly on oracle.
|
6/27/2002 12:36:07 PM:Roger
excelent
|
7/1/2002 4:44:51 PM:vinnie
why not use ">=" in
"where b.itemid >
a.itemid" so that you don't need to
subtract 1 from n?
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this code has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular code, please click here.
|
|
