asked
SQL Data retrieval
I am trying to write a SELECT statement to retrieve records from emp table on Scott user. But not getting answer. The question is : display empno, ename, sal, max_sal_in_his_dept, sal+12% on existing salary of those employes whose salary is below 1/3rd of maximum salary in their respective departments. i tried group by with where clause, didnt work, tried group by with having but not working, Plz help
Here is my shot at that, but I am not sure of it. I could have test it if some test data would be there. WITH DepatmentSalary as ( SELECT MAX(salary) as maxsalary, department from employee group by department ) SELECT empno, ename, sal, max_sal from employees CROSS APPLY ( SELECT TOP 1 MAX(salary) max_sal FROM DepatmentSalary WHERE employees.department = DepatmentSalary.department ) where salary < ( select TOP 1 maxsalary/3 from DepatmentSalary WHERE employees.department = DepatmentSalary.department)
