Re: Selecting 2nd Largest Value

On Mon, 21 Jan 2002 13:17:32 -0500, "John D. Boshears" <jbosh_at_purdue.edu> wrote:

>How would I go about selecting the second largest value from a table? Not
>the second in order, but the second largest. I know in Access you could
>combine TOP and MIN functions, but I can't find a function similar to TOP
>in Oracle.
>
>thanks.
>
>John
>

In 8i you can try:

SELECT Value FROM
 (SELECT Value, ROWNUM RN FROM
  (SELECT Value FROM MyTable ORDER BY Value DESC)    WHERE ROWNUM < 3)
WHERE RN = 2 If the column does not have a UNIQUE constraint on it, the second highest may be the highest as well, and then you'll can use either a DISTINCT or a GROUP BY in the innermost query.

SELECT Value FROM
 (SELECT Value, ROWNUM RN FROM
  (SELECT DISTINCT Value FROM MyTable ORDER BY Value DESC)    WHERE ROWNUM < 3)
WHERE RN = 2 If you're just getting the second highest value, the solutions above may be a bit too elaborate. If you just want the second greatest, and only the second greatest:

SELECT MAX(Value) FROM MyTable
WHERE Value NOT IN (SELECT MAX(Value) FROM MyTable);

HTH,
Brian Received on Mon Jan 21 2002 - 13:10:30 CST