how to retrieve highest and lowest salary from following table?

Question

I have employee table

 EMP_ID | F_NAME    | L_NAME  | SALARY | JOINING_DATE                 | DEPARTMENT
-----------------------------------------------------------------------------------
 101    | John      | Abraham | 100000 | 01-JAN-14 09.15.00.000000 AM | Banking
 102    | Michel    | Clarke  | 800000 |                              | Insaurance
 102    | Roy       | Thomas  | 70000  | 01-FEB-13 12.30.00.000000 PM | Banking
 103    | Tom       | Jose    | 600000 | 03-FEB-14 01.30.00.000000 AM | Insaurance
 105    | Jerry     | Pinto   | 650000 | 01-FEB-13 12.00.00.000000 PM | Services
 106    | Philip    | Mathew  | 750000 | 01-JAN-13 02.00.00.000000 AM | Services
 107    | TestName1 | 123     | 650000 | 01-JAN-13 12.05.00.000000 PM | Services
 108    | TestName2 | Lname%  | 600000 | 01-JAN-13 12.00.00.000000 PM | Insaurance

i want to find highest and lowest salary from above table in oracle sql. if i do

 select max(salary) from (select * from (select salary from employee) where   rownum <2);

it returns MAX(SALARY) = 100000 where it should return 800000

If I do

select max(salary)
  from (select * from (select salary from employee) 
         where rownum <3);

it returns MAX(SALARY) = 800000

If I do

select min(salary)
  from (select * from(select salary from employee)
         where rownum < 2);

it will return MIN(SALARY) = 100000 where it should return 70000.

What is wrong in this query?

what should be the correct query?

What is the datatype on the salary column? Do you need to to_number() it? — Andrew Mortimer, Jul 30 '15 at 07:19
Side note: using `rownum` filters without `order by` clauses is usually, if not always, a bug. — Mat, Jul 30 '15 at 07:21
ROWNUM will not ensure the order of the rows returned. It is just a pseudo column and is assigned after the rows are returned. And without an `ORDER BY` there is no guarantee of the order and rows are returned randomly. See http://stackoverflow.com/a/30321788/3989608 — Lalit Kumar B, Jul 30 '15 at 08:19
You should choose correct answers if they helped you. You're not giving people who answer your questions any credit at the moment. — Chris Dennett, Aug 16 '15 at 10:00

score 4 · Answer 1 · answered Jul 30 '15 at 07:20

4

You don't need all these subqueries:

SELECT MAX(salary), MIN(salary)
FROM   employee

answered Jul 30 '15 at 07:20

Mureinik

297,002
52
306
350

then if i want to find 2nd highest and lowest salary then what should be the query? – Thompson rollins Jul 30 '15 at 07:30

MT0 · Answer 2 · 2015-07-30T07:54:14.957

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE employee ( EMP_ID,  F_NAME,  L_NAME,  SALARY,  JOINING_DATE, DEPARTMENT ) AS
          SELECT 101, 'John',      'Abraham', 100000, TIMESTAMP '2014-01-01 09:15:00', 'Banking'   FROM DUAL
UNION ALL SELECT 102, 'Michel',    'Clarke',  800000, NULL,                            'Insurance' FROM DUAL
UNION ALL SELECT 102, 'Roy',       'Thomas',   70000, TIMESTAMP '2013-02-01 12:30:00', 'Banking'   FROM DUAL
UNION ALL SELECT 103, 'Tom',       'Jose',    600000, TIMESTAMP '2014-02-03 01:30:00', 'Insurance' FROM DUAL
UNION ALL SELECT 105, 'Jerry',     'Pinto',   650000, TIMESTAMP '2013-02-01 12:00:00', 'Services'  FROM DUAL
UNION ALL SELECT 106, 'Philip',    'Mathew',  750000, TIMESTAMP '2013-01-01 02:00:00', 'Services'  FROM DUAL
UNION ALL SELECT 107, 'TestName1', '123',     650000, TIMESTAMP '2013-01-01 12:05:00', 'Services'  FROM DUAL
UNION ALL SELECT 108, 'TestName2', 'Lname%',  600000, TIMESTAMP '2013-01-01 12:00:00', 'Insurance' FROM DUAL;

Query 1 - To find the highest-n salaries:

SELECT *
FROM   (
  SELECT salary
  FROM   employee
  ORDER BY salary DESC
)
WHERE  rownum <= 3 -- replace with the number of salaries you want to retrieve.

Results:

| SALARY |
|--------|
| 800000 |
| 750000 |
| 650000 |

Query 2 - To find the lowest-n salaries:

SELECT *
FROM   (
  SELECT salary
  FROM   employee
  ORDER BY salary ASC
)
WHERE  rownum <= 3 -- replace with the number of salaries you want to retrieve.

Results:

| SALARY |
|--------|
|  70000 |
| 100000 |
| 600000 |

score 0 · Answer 3 · answered Jul 30 '15 at 07:28

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

So in your case :

select max(salary) from (select * from (select salary from employee) where   rownum <2);

This query will return

101    John     Abraham 100000  01-JAN-14 09.15.00.000000 AM    Banking

only this row as output... and hence the max value will be 100000 only.

select max(salary) from (select * from (select salary from employee) where  rownum <3);

This query will tak first 2 rows from your table, i.e.,

101    John     Abraham 100000  01-JAN-14 09.15.00.000000 AM    Banking
102    Michel   Clarke  800000                                Insaurance

and hence the max salary will be 800000.

Similarly,

select min(salary)from (select * from(select salary from employee)where rownum<2);

will only select 1st row

select min(salary)from (select * from(select salary from employee)where rownum<2);

so min salary will be 100000.

P.S. : You could simply write your queries like this :

select max(salary) from  employee where rownum<[n];

where n will be ROWNUM to which you want to limit the number of rows returned by your query

*select max(salary) from employee where rownum<[n];* **You are completely wrong**. ROWNUM will not ensure the order of the rows returned. It is just a pseudo column and is assigned after the rows are returned. And without an `ORDER BY` there is no guarantee of the order and rows are returned randomly. See http://stackoverflow.com/a/30321788/3989608 — Lalit Kumar B, Jul 30 '15 at 08:18

score 0 · Answer 4 · answered Jul 30 '15 at 07:38

0

Try it:

select *
from (
  select T.*, rownum RRN
  from (
    select salary
    from employee
    order by salary desc) T)
where RRN < 3

answered Jul 30 '15 at 07:38

saphsys

96
4

score 0 · Answer 5 · answered Jul 30 '15 at 10:38

0

so you want the 2nd highest and 2nd lowest salary? Check this out

select max(salary), min(salary) from employee
where salary < (select max(salary) from employee)
and salary > (select min(salary) from employee)
;

answered Jul 30 '15 at 10:38

PT_STAR

505
1
4
13

score 0 · Answer 6 · answered Sep 22 '18 at 08:00

1) For lowest salary.
select * from (
               select empno,job,ename,sal 
                   from emp order by sal) 
                          where rownum=1;

2) For Highest salary.
select * from (
               select empno,job,ename,sal 
                   from emp order by sal desc)
                          where rownum=1;

score 0 · Answer 7 · edited Apr 20 '20 at 09:54

0

i don't know why you make complicated queries you can simply write this and get the same result:

select salary
from employees
where rownum <=3
order by salary desc;

edited Apr 20 '20 at 09:54

Boken

4,825
10
32
42

answered Apr 20 '20 at 08:48

adfinem_rising

45
1
8

1

Rownumber will not guarantee correct results as it will most likely filter before the sorting. It looks like the goal is to find the highest and lowest salary, and that it is being done with two separate queries. Using the SELECT TOP 1 query would solve this together with ORDER BY salary DESC and ORDER BY salary ASC. Hope it helps. – Tanaka Saito Apr 20 '20 at 09:43

score 0 · Answer 8 · edited Aug 14 '20 at 09:44

you can solve your problem with following queries:

Highest salary:

Select * from Employee(Select salary from Employee ORDER BY salary DISC) where rownum=1;

Lowest salary:

Select * from Employee(Select salary from Employee ORDER BY salary) where rownum=1;

Second highest salary:

Select MAX(Salary) from Employee 
Where Salary < (Select MAX(Salary) from employee);

Second Lowest salary :

Select MIN(Salary) from Employee 
Where Salary > (Select MIN(Salary) from employee);

how to retrieve highest and lowest salary from following table?

8 Answers8