x

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
more ▼

asked Jan 20, 2012 at 01:58 AM in Default

Meena gravatar image

Meena
21 1 1 1

Can you please provide some test data for this?
Jan 20, 2012 at 02:28 AM robbin
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)
more ▼

answered Jan 20, 2012 at 02:31 AM

robbin gravatar image

robbin
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x112

asked: Jan 20, 2012 at 01:58 AM

Seen: 578 times

Last Updated: Jan 20, 2012 at 01:58 AM