Oracle SQL Question:
Download Job Interview Questions and Answers PDF
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
select max(sal)"nth min sal" from(select distinct sal from emp order by sal) where rownum<=&N
'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
select max(sal)"nth min sal" from(select distinct sal from emp order by sal) where rownum<=&N
Download SQL Oracle Interview Questions And Answers
PDF
| Previous Question | Next Question |
| Explain What should be the return type for a cursor variable. Can we use a scalar data type as return type? | Explain What is the purpose of a cluster? |