SQL - minvalue.sql Select the Nth lowest value from a table

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 27 of 169

minvalue.sql Select the Nth lowest value from a table

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

Total Answers and Comments: 7

Last Update: October 07, 2007

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

August 02, 2006 12:38:51

Rama Krishna,Yerra

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

1)By Using Rownum we can do the same mechanisam..

Select rownum,empno,ename,sal from(select rownum,empno,ename,sal from emp order by sal) having rownum=&n;

2) select empno,sal from emp e where &n>(select min(sal) from emp where sa l<e.sal);

Thanks&Regds

Ramki,TCS,DP HYd

Is this answer useful? Yes | No

October 13, 2006 08:51:51

Raveendra B N

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

select value from <table name> a

where n = (select count(distinct value)

from <table name> b

where a.value <= b.value)

Is this answer useful? Yes | No

December 12, 2006 09:11:27

rampratap409

Member Since: September 2006 Contribution: 111

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;

select * from ( select rownum rnum, a.* from <tablename>

order by sal desc)

where rnum = &nth

Is this answer useful? Yes | No

April 03, 2007 16:01:03

tina_0091

Member Since: April 2007 Contribution: 5

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

Use the top-analysis feature which introduce from oracle 8i:

SELECT rownum, empno, ename, sal
FROM (SELECT empno, ename, sal from emp
ORDER BY sal )
WHERE rownum < n; (n=any valid integer)

Thanks,
Tina K.

Is this answer useful? Yes | No

July 21, 2007 14:53:49

soumyapradh

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

Try this one

SELECT LEVEL, MIN(Sal)
FROM Emp
WHERE LEVEL = &LevelNo
CONNECT BY PRIOR Sal < Sal
GROUP BY LEVEL
/

Is this answer useful? Yes | No

October 04, 2007 07:35:44

Ishita

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

Easiest way

select top 1 name from emp where name>(select min(name) from emp) order by name

Ishita.

Is this answer useful? Yes | No

October 07, 2007 02:47:21

NITYANAND

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

TO find out nth LOWEST value, e.g. salary from emp table without using ROWNUM & DESC/ASC

SELECT DISTINCT (a.sal) FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT (b.sal))
FROM EMP B
WHERE a.sal >= b.sal);

Is this answer useful? Yes | No

Go To Top