Re: Second highest value

Try this query
select sal from emp a where &n = (
select count(distinct(sal)) from emp b where a.sal <= b.sal )

When you execute this query it will prompt you an input - enter the sal position you want. You can also make it a part of an cursor , pass the sal position as an parameter.

Sunil

Juhan Kundla <juhan_at_ensib.ee> wrote:
>jegatheesan wrote:
>>
>> Try using this command
>>
>> select max(data) from temp where data < (select max(data)
from temp)
>>
>> By,
>> jegatheesan.A
>> --
>> Posted via Talkway - http://www.talkway.com
>> Exchange ideas on practically anything (tm).
>
>Consider this:
>
>SQL> CREATE TABLE t (n NUMBER);
>
>Table created.
>
>SQL> INSERT INTO t VALUES (1);
>
>1 row created.
>
>SQL> INSERT INTO t VALUES (2);
>
>1 row created.
>
>SQL> INSERT INTO t VALUES (3);
>
>1 row created.
>
>SQL> COMMIT;
>
>Commit complete.
>
>Now I have a test table. I'm going to try your solution to get
the
>second highest value.
>
>SQL> SELECT MAX(n)
> 2 FROM t
> 3 WHERE n < (
> 4 SELECT MAX(n)
> 5 FROM t);
>
> MAX(N)
>---------
> 2
>
>OK. This works fine. But what if I want to get third highest
value. I'll
>do like was suggested.
>
>SQL> SELECT MAX(n)
> 2 FROM t
> 3 WHERE n < (
> 4 SELECT MAX(n)
> 5 FROM t
> 6 WHERE n < (
> 7 SELECT MAX(n)
> 8 FROM t));
>
> MAX(N)
>---------
> 1
>
>Bingo!
>
>But imagine if I had a table with 239 002 records and I
desperately need
>to sort out a record, which by some kind of criteria has the
3674-th
>highest value. If I kept using the same method, then writing
the query
>is harder than 10 jobs of Hercules and before getting results
from
>database I would be eating icecream in heaven.
>
>It is hard to me to believe, that there is no more decent way
to query
>out the x-th highest value from database than this. Can
somebody share
>his knowledge about how this can be done.
>
>--
>Ilusat suve...
>
> \ | /
> \ | /
>-----( )-----
> / | \
> / | \
>
>....ja päikest :)
>
>Juhan
Received on Wed Aug 11 1999 - 10:29:51 CDT