Question

1. There is a table t with a column salary which is the primary key. Write a query to get the nth minimum salary without using multi query or limit. You will be awarded no points if you use multi query or limit or rownum or rowid.

no subquery are allowed. please solve it with joins

Answer 1

Solution:

Creating a Table:

create table t(salary int primary key);

Inserting values into table:

insert into t values (10000),(20000),(30000),(11000),(19000),(90000);

Query:

select t.salary from t join t t1 where t1.salary <= t.salary group by t.salary having count(distinct t1.salary)=2;

Explanation:

In above query joined the table t with itself that means considered table 't' as two instances.since it contains only one column no need to mention any join condition . In group by clause grouping salary column in first table so that salary is compare with each and every salary in second table and counting in having clause where the count is equal to given n value.

In the query we can replace the n value with 2 so that we can get nth highest salary.it's better to put in a procedure and call the procedure with passing n value as parameter.

Here procedure code:

delimiter $$
create procedure nth_minimum_salary(in n int)
begin
select t.salary from t join t t1 where t1.salary <=t.salary group by t.salary having count(distinct t1.salary)=n;
end $$

Calling Procedure:

call nth_minimum_salary(3) $$

call nth_minimum_salary(2) $$

Code and Output Screenshots:

Note : if you have any queries please post a comment thanks a lot..always available to help you...