how to find less than the largest number in sql?-CodePudding

i want to show the salaries less than the largest number in the table


create table instructor(

ID number not null , 
name varchar(20) ,
dept_name varchar(25),
salary number,
primary key (ID)

);

insert into instructor values('10101', 'sirinvasan' , 'comp csi' , '65000');
insert into instructor values('12121', 'wu' , 'finance' , '90000');
insert into instructor values('15151', 'mozart' , 'music' , '40000');
insert into instructor values('22222', 'einstein' , 'physics' , '95000');
insert into instructor values('32343', 'el said' , 'history' , '60000');

i tried using

select distinct T.salary
from instructor as T , instructor as S
where T.salary < S.salary

but it gives me this error ORA-00933: SQL command not properly ended

CodePudding user response：

You can do it in a single table scan using analytic functions:

SELECT salary
FROM   (
  SELECT DISTINCT
         salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM   instructor
)
WHERE rnk > 1

Or, if you want to use a sub-query:

SELECT DISTINCT
       salary
FROM   instructor
WHERE  salary NOT IN (SELECT MAX(salary) FROM instructor);

db<>fiddle here

CodePudding user response：

Try this

  Select salary from table where salary < (Select 
   max(salary) from table)

CodePudding user response：

There are many ways to do this. Here is another one.

with a as (
select salary
, max(salary) over () as maxsalary
from instructor
)
select distinct salary
from a
where salary < maxsalary

CodePudding user response：

In the name of completeness, here is another viable answer:

SELECT DISTINCT i.salary
FROM instructor i
ORDER BY i.salary DESC
OFFSET 1 ROW;

I actually like the simplicity of this one. It is just ordering the query and skipping the first row. No analytic functions necessary.

DBFiddle Here: (LINK)