These are important for interview and reference for other needs in day to day jobs.
- ) Second Highest Salary: We can achieve this using - MAX , LIMIT and TOP
select max(salary) from EMP where salary < (select max(salary) from EMP)
select max(salary) from EMP where salary not in(select max(salary) from EMP)
select * from (
select a.*, row_number() over (order by salary desc) as row_num from Emp a
) where row_num = 2;
select * from ( select a.*, rank() over (order by salary desc) as rank from Emp a ) where rank = 2;
LIMIT: select salary from ( select salary from EMP ORDER BY salary DESC LIMIT 2) AS EMP ORDER BY salary limit 1;
- Here the inner sql will return the top 2 rows in desc order.
- last order by will arrange again in ascending and return first row.
TOP: Select Top 1 salary from ( select Top 2 salary from EMP ORDER BY salary desc) AS EMP1 ORDER BY salary ASC;
2.) QUERY to find duplicate records in table.
select emp_name from EMP group by emp_name having count(emp_name) > 1;