Answer Detail: RE: minvalue.sql Select the Nth lowest value from a ta...

GeekInterview.com > Interview Questions > Oracle > SQL

Print |

Question: minvalue.sql Select the Nth lowest value from a table

Answer:

select level, min('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 lowest salary:

-- select level, min(sal) from emp

-- where level=2

-- connect by prior sal < sal

-- group by level

December 12, 2006 09:11:27	#3
rampratap409	Member Since: September 2006 Total Comments: 108
RE: minvalue.sql Select the Nth lowest value from a ta...
select * from <tablename> where rownum<= &nth order by sal desc; minus select * from <sametablename> where rownum<&nth order by sal desc; or select * from ( select rownum rnum, a.* from <tablename> order by sal desc) where rnum = &nth