Hi.
Try this, I got from orafaq.com
rem -----------------------------------------------------------------------
rem Filename: maxvalue.sql
rem Purpose: Select the Nth highest value from a table
rem Date: 18-Apr-2001
rem Author: Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------
select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;
- Example :
--
- Given a table called emp with the following columns:
- id number
- name varchar2(20)
- sal number
--
- For the second highest salary:
--
- select level, max(sal) from emp
- where level=2
- connect by prior sal > sal
- group by level
--
I haven't tried it, but perhaps Level is a reserved word.Arthur."music4"
<music4_at_163.net> wrote in message
news:bgklkh$qnb_at_netnews.proxy.lucent.com...
> Greetings,
>
> Say I have a table with two columns: name, score. I want to get name of
the
> highest score. I wrote following SQL statement:
>
> select name from tbl where score = (
> select max(score) from tbl)
>
> The statement includes two select, is there a way to use only one select
to
> get the name of highest score's name?
>
> Thanks in advance!
> Evan
>
>
Received on Mon Aug 04 2003 - 04:06:39 CDT
