Skip to main content

Calculating nth Max salary

This is a very common question in any interview on SQL. Max salary can be calculated in different ways. Following are the few examples and their explanation.

1. Using simple max function. (Retruns First Max Salary only)

SQL> SELECT MAX(sal)
           FROM emp;

 MAX(SAL)
----------
      5000

This is to find the first max salary from EMP table. MAX is a group function which returns maximum value out of group of records or all the records.

Click Here, to know more about MAX

2. Using subqueries.

SQL> SELECT MAX(sal)
           FROM emp
           WHERE sal < (SELECT MAX(sal) FROM emp);

  MAX(SAL)
----------
      3000

Look at the above query closely, sal < max(sal) returns all the salaries less than first max salary. Calculating maximum salary out of all the salaries less than the first maximum salary will return second maximum salary.

So use multiple subqueries to get nth max salary.

3. Using ROWNUM

ROWNUM is a psuedocolumn which retruns a number indicating the order in which Oracle selects the data.

SQL> SELECT Salary
           FROM (SELECT rownum MAXSALARY, sal Salary FROM ( SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC))
           WHERE MAXSALARY = 3;

    SALARY
----------
      2975

Change the MAXSALARY condition to nth value to get nth max salary.
Click Here, to know more about ROWNUM.


4. Using MIN() function along with ROWNUM to calculate nth maximum salary.

SQL> SELECT MIN(sal)  FROM emp WHERE sal IN (SELECT sal FROM (SELECT DISTINCT(sal) FROM emp ORDER BY sal DESC) where rownum<=3)

  MIN(SAL)
----------
      2975

5. Using LEVEL

SQL> SELECT LEVEL, MAX(sal) FROM emp
WHERE LEVEL = 3
CONNECT BY PRIOR sal>sal
GROUP BY LEVEL;

     LEVEL   MAX(SAL)
---------- ----------
         3       2975

6. Using Co-Related Subqueries

SELECT * FROM emp r1
WHERE &n = (SELECT COUNT(DISTINCT(sal))
FROM emp WHERE sal >= r1.sal);



Comments

kakali said…
i want to know,how the last query for nth max salary is executed?please explain me
Gyanranjan Dash said…
SQL> SELECT * FROM EMP r1
2 WHERE &n=(SELECT COUNT(DISTINCT(SAL))
3 FROM EMP WHERE SAL>=r1.SAL);
Enter value for n: 1
This is for max sal.You can put any no like 1,2,3,4,5 for the "n" no of max salary.
Mithun Ashok said…
Hi Kakali,

The last query for nth max salary uses co-related subqueries to find nth max salary. Read through my presentation on Subqueries HERE .
JAGADISH said…
hi sir,
I created a table like for eg:
create table t1(name varchar2(20),
cur blob)
/
here i want to insert the blob value but i could gettin how to insert blob values.Please help me on this.
Ashwini said…
Sir how to Display job-wise average salaries for the employees whose employee number is not from 7788 to 7790.
Mithun Ashok said…
Hi Jagadish,

BLOB datatypes are Binary in nature. So try inserting 1 or 0 within quotes and this will work and to display these values you will have to convert them to char or number.

Regards,
Mithun
Mithun Ashok said…
Hi Ashwini,

Here is the answer.

SQL> select job, avg(sal) from emp where empno not in (7788,7790) group by job;

Mithun
Sadashiv said…
Below is my table:class
FName LName Sex
ABC PQR MALE
XYZ MNO FEMALE
EGF STU MALE

My question is I want to insert one more column Name Phone in between LName and Sex.
Mithun Ashok said…
Hi Sadhashiv,

This is not possible, infact it does not matter if Name, Phone are at the beginning, middle or at the end. All you have to do it to select the columns in what ever sequence you want while selection.

If at all you want this to be in the structure when you describe or do a select * from the table then you need to drop the entire table and recreate it again with required sequence.

Hope this is clear.

Regards,
Mithun
sir can we put check condition in ER diagram like DFD?
like yes than go in this entity otherwise in other entity.....
gyanendra(jini) said…
HELLO SIR
THANKS FR TEACHING SO WELL
I FOUND CASE MANIPULATION FUNCTIONS VERY INTERESTING AFTER Y0U GAVE US ASSIGNMENTS ON THIS.
Simpy said…
Hi Sir
maine pehle v sql padhne ki koshis ki thi ...and i don't knw y..mujhe laga main nhi sikh paungi..but, thanks to u...aap itna mast padhate ho ki jo na sikhna chahe wo v SQL sikhne aa jaye...WE LOVE YOU...............
Shiv Pratap said…
hello Sir,,
please tell me difference between MSSQL And Oracle as point interview....please mail me pratap852006@gmail.com(BCE-2 batch Bull Temple Road)

thanx
Thanmathpradeep said…
hello sir,
please check the image wch i had attached to this message ... and please mail me the corresponding query to my mail id i.e., thanmathpradeep@gmail.com ................ it will be much useful for me if u send d query asap.........

thanks & regards,
Hanmath Pradeep
thanmathpradeep@gmail.com
9160160096
HHH said…
how to find only duplicate records
Ramana said…
Hi Ashok,

how to display five minimum salaries from emp table without any subquery
RaviChandra said…
1) First Executes This Query

SELECT Deptno,Ename,Sal,(SELECT SUM(Sal) FROM Emp E2 WHERE E2.Deptno= E1.Deptno)SumSal FROM Emp E1

ORDER BY Deptno

2.Execute This Statement

BREAK ON SumSal SKIP 1

3.Again Executes This Query
SELECT Deptno,Ename,Sal,(SELECT SUM(Sal) FROM Emp E2 WHERE E2.Deptno= E1.Deptno)SumSal FROM Emp E1

ORDER BY Deptno

/

/
suman said…
sir,if max of salary is taken from different tables.how to merge the columns retrieved into a single column.
shibin ps said…
hi sir,
in correlation query, does the value from inner query sends each time to outer query after the complete iteration of the table used by inner query?
Anonymus! said…
Sir,
If max of salary is taken from different tables, how to merge the columns retrieved into a single column?
Deo Narayan said…
Hi sir
Display the list of employees who are earning 2nd highest salary in their respective departments without using corelated subqueries.
Deo Narayan said…
Hi sir,
Write a query to display the 1st day of current month..
Deo Narayan said…
Hi sir,
Write a query to display the 1st day of current month..
Deo Narayan said…
This comment has been removed by the author.
Rayeesa Dadwad said…
Hi Sir
Write a query to fetch the name of the employees with top 5 salaries.
Please Reply
Anonymous said…
Hello Sir,
i want to display 10th maximum salary from emp table is this query correct..?
select * from emp x
where &10=(select count(DISTINCT(sal))
from emp where sal>=x.sal;
Unknown said…
Hello sir,
This is SADHANA
i want to display SQL query to find duplicate row in database? Is this query correct...?
select * from emp x
where rowid=( select max(rowid) from emp
where empno= x.empno);

Reply

Popular posts from this blog

SQL Interview Questions on Subqueries

SUB Queries:
1. List the employees working in research department 2. List employees who are located in New York and Chicago
3. Display the department name in which ANALYSTS are working
4. Display employees who are reporting to JONES
5. Display all the employees who are reporting to Jones Manager
6. Display all the managers in SALES and ACCOUNTING department
7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them
8. Display all employees who do not have any reportees
9. List employees who are having at least 2 reporting
10. List the department names which are having more than 5 employees
11. List department name having at-least 3 salesman
12. List employees from research and accounting having at-least 2 reporting
13. Display second max salary
14. Display 4th max salary
15. Display 5th max salary  -- Answer for nth Max Salary
Co-Related Subqueries:
16. Write a query to get 4th max salary from EMP table
17. Write a query to get 2nd…

'Linux-x86_64 Error: 28: No space left on device' While trying to start the database -- Error

SQL> startup mount pfile='/tmp/initdlfasp12.ora'
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device


This as you can see is on Linux x86 with 64 bit processor. We got this error after we changed SGA on 10gR2 database. So was sure that this is something to do with the OS.

Parameters to check for this are shmall.

shmall is the total amount of shared memory, in pages, that the system can use at one time.

$cat /proc/sys/kernel/shmmax
53687091200

$ getconf PAGE_SIZE
4096

As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size.

Our system has 64GB memory, so change kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216


Once this value is calculated you can modify Linux system configuration file directly.

$ su - root
vi /etc/sysctl.conf file:
kernel.shmall=16777216

and

# sysctl -p

Once this is done the database was started without any problem.

Answers for SUB Queries

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH');


2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));

3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');

4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');

5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')

6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))

7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)

8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)

9. select…