MVSFORUMS.com

[butta_mvs]

Here iam presenting some sql queries, Critiques are welcomed.


1) TO RETRIEVE N MAXIMUM SALARIES
select salary from employee e where 3 >
(select count(*) from employee where salary > e.salary) ORDER BY E.SALARY


***************************************************************************************************************
2) TO RETRIEVE Nth MAXIMUM SALARIEY
select '3rd max salary ' , min(salary) from employee where salary in
(
select salary from employee e1 where 3 >
(
select count(*) from employee e2 where e2.salary > e1.salary
)
)

***************************************************************************************************************
3) TO RETRIEVE N MINIMUM SALARIES
select salary from employee e where 3 >
(select count(*) from employee where salary < e.salary) order by e.salary

***************************************************************************************************************
4) TO RETRIEVE Nth MINIMUM SALARIEY
select '3rd min salary ' , max(salary) from employee where salary in
(
select salary from employee e1 where 3 >
(
select count(*) from employee e2 where e2.salary < e1.salary
)
)

***************************************************************************************************************
5) TO RETRIVE SUM OF N MAXIMUM SALARIES
select sum(salary) from employee where salary in
(select salary from employee e where 3 >
(select count(*) from employee where salary > e.salary))

***************************************************************************************************************
6) TO RETRIVE SUM OF N MINIMUM SALARIES
select sum(salary) from employee where salary in
(select salary from employee e where 3 >
(select count(*) from employee where salary < e.salary))

***************************************************************************************************************

7) TO RETRIEVE SUM OF Nth MAX and Nth MIN

select 'sum of 3rd max and 3rd min ' , sum(salary) from employee where salary in
(
select min(salary) from employee where salary in
(
select salary from employee e1 where 3 >
(
select count(*) from employee e2 where e2.salary > e1.salary
)
)

union

select max(salary) from employee where salary in
(
select salary from employee e1 where 3 >
(
select count(*) from employee e2 where e2.salary < e1.salary
)
)
)

***************************************************************************************************************

8) DIFFERENCE BETWEEN Nth MAX AND Nth MIN

select 'diff of 3rd max and 3rd min ' , max(salary) - min(salary) from employee where salary in
(
select min(salary) from employee where salary in
(
select salary from employee e1 where 3 >
(
select count(*) from employee e2 where e2.salary > e1.salary
)
)

union

select max(salary) from employee where salary in
(
select salary from employee e1 where 3 >
(
select count(*) from employee e2 where e2.salary < e1.salary
)
)
)

***************************************************************************************************************

TO RETRIEVE N MAX SALARIES FROM EACH DEPARTMENT

select dept , salary from employee e where 3 >
(select count(*) from employee where salary > e.salary and dept=e.dept) order by dept , salary desc

***************************************************************************************************************

TO RETRIEVE N MIN SALARIES FROM EACH DEPARTMENT

select dept , salary from employee e where 3 >
(select count(*) from employee where salary < e.salary and dept=e.dept) order by dept , salary asc

***************************************************************************************************************
======== Ramesh Butta=========

[kolusu]

Ramesh Butta,

I appreciate you posting these queries but these are already discussed in this forum. Queries 1 & 2 are same as queries 9 & 10. All your queries are discussing the top 3 even though you have specified for top "N".

Check the following links which discuss the exact same queries.

http://www.mvsforums.com/helpboards/viewtopic.php?t=23

http://www.mvsforums.com/helpboards/viewtopic.php?t=1083

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu