How to find the Nth largest or Nth smallest salary or number in a table?

Hi,

below query is to find the Nth max value in a table:

SELECT min(empno)
FROM(SELECT DISTINCT(empno)
FROM employee
ORDER BY empno desc)
WHERE ROWNUM<= N;

below query is to find the Nth max value in a table:

SELECT max(empno)
FROM(SELECT DISTINCT(empno)
FROM employee
ORDER BY empno asc)
WHERE ROWNUM<= N;

Maximum sal

select sal maxsal from (select sal, rn from (select sal, rownum rn from scott.emp order by sal desc) where rn = &rn)

or minimum sal

select sal minsal from (select sal, rn from (select sal, rownum rn from scott.emp order by sal asc) where rn = &rn)

__________________
sireesha

use max and min function in your query

Please row_number() over(order by Salary)
within derived table & outside row

Select row,Salary
from (Select row_number() over(order by Salary) row, Salary from table1)
Where row = Nth

Hi,

select max(sal) from emp where sal<
(select max(sal) from emp where sal<(select max(sal) from emp))

select max(sal) from emp where sal<
(select max(sal) from emp where sal<(select max(sal) from emp))

select max(sal) from emp where sal<
(select max(sal) from emp where sal<(select max(sal) from emp))

what is the difference between dbms and rdbms?

See ,N largest number and N smallest number means the largest and the smallest
value from the column of the given table This can be fine out by the max and min function.

Hi Jitendra,

DBMS is a ordinary system to maintain the data, whereas RDBMS is the specific type, which follows the concepts of set theory

i.e entity etc.

In DBMS there is no concept of PRIMARY KEY and FOREIGN KEY but it is included in RDBMS.

DBMS contains only flat data whereas in RDBMS there will be some relation between the entities.

If u want to know more differences here is this...


DBMS:

1)In dbms no relationship concept
2)It supports Single User only
3)It treats Data as Files internally
4)It supports 3 rules of E.F.CODD out off 12 rules
5)It requires low Software and Hardware Requirements.
6)FoxPro, IMS are Examples

RDBMS:

1)It is used to establish the relationship concept between two database objects, i.e, tables
2)It supports multiple users
3)It treats data as Tables internally
4)It supports minimum 6 rules of E.F.CODD
5)It requires High software and hardware requirements.
6)SQL-Server, Oracle are examples

&&
one thing ...

Plz post u r queries starting with a new thread...

Thanks & Regards,
Riju.

select max(col_name),min(col_name) from table_name;

select sal from emp where sal >=all(select sal from emp);

How can i create procedure in sql for emp... Please tell me some same procedure for emp table from scott, tiger.....

Do you know how to create procedure for emp table to extract all the highest salary holder from all the departments....

@rajabose, why need to go for stored procedures, we can achieve this, by sql analytic functions. For ur reference : the above sql analytic functions retrieves the details of orderwise highest paid employees in each dept.

this query is helpfull for you

select * from emp e where &n=(select count(sal) from emp w where e.sal