September 28, 2011 at 9:54 am
I have 2 tables Department and Employee and I want to find for each department, output the name of the employee with the highest salary.
Department table
eid dept_name jobtitle salary managerid
1Finance Manager100002
2Sales Sales Man1000 3
3Help CEO 1000001
4Finance CEO 700004
Employee table
eid employee_name street city
1Kunjan 97thOzone Park
2Kiran 98thsandamen
3Mintu 101 vashi
4Heena 91Richmond
Tried running this query
select E.employee_name,W.dept_name, MAX(W.salary) from
Employee AS E join Works AS W
on E.eid = W.eid
group by E.employee_name,W.dept_name
but its not giving me the max salary for a particular dept.
Can you please let me know what going wrong
September 28, 2011 at 10:05 am
kbartakke (9/28/2011)
I have 2 tables Department and Employee and I want to find for each department, output the name of the employee with the highest salary.Department table
eid dept_name jobtitle salary managerid
1Finance Manager100002
2Sales Sales Man1000 3
3Help CEO 1000001
4Finance CEO 700004
Employee table
eid employee_name street city
1Kunjan 97thOzone Park
2Kiran 98thsandamen
3Mintu 101 vashi
4Heena 91Richmond
Tried running this query
select E.employee_name,W.dept_name, MAX(W.salary) from
Employee AS E join Works AS W
on E.eid = W.eid
group by E.employee_name,W.dept_name
but its not giving me the max salary for a particular dept.
Can you please let me know what going wrong
Try a derived table like this
Select e.employee_name, w.Dept_name, w.Salary
from
dbo.Employee E INNER JOIN
(select w.[dept_name], w.[eID], Salary = MAX(w.[Salary])
FROM dbo.Works w GROUP BY w.dept_name, w.eID) as w ON e.eid = w.eid
September 28, 2011 at 10:58 am
I tried to use your query but again its giving me all results and It didnt filter anything.
September 28, 2011 at 11:20 am
kbartakke (9/28/2011)
I tried to use your query but again its giving me all results and It didnt filter anything.
Then you need to post sample data and the DDL. The query I gave was a shot in the dark as the Works table is not mentioned in your original post.
September 28, 2011 at 12:32 pm
You can partition by department name and dense rank by salary desc like so:
select dense_rank() over(partition by d.dept_name order by d.salary desc) as 'rank', e.eid, e.employee_name, d.dept_name, d.salary
from employee e
inner join department d
on e.eid = d.eid
If you don't want to see the ranking number, just put the above query in a CTE and select what you want out of it.
with cte as (
select dense_rank() over(partition by d.dept_name order by d.salary desc) as 'rank', e.eid, e.employee_name, d.dept_name, d.salary
from employee e
inner join department d
on e.eid = d.eid
)
select eid,employee_name,salary
from cte
Let me know if you need it explained more in-depth.
September 29, 2011 at 3:37 am
can you post your DDL properly with constraint of two tables. as per me your table design is not correct.
you should have deptid in emp table as department is a higher entity then employee.
although post your DDL if we can help.
September 29, 2011 at 7:53 am
ghanshyam.kundu (9/29/2011)
can you post your DDL properly with constraint of two tables. as per me your table design is not correct.you should have deptid in emp table as department is a higher entity then employee.
although post your DDL if we can help.
I would second what ghanshyam said. The table structure looks like it needs some serious work. You have an employeeID in the department table. This means you have a 1:1 relationship unless a single employee can hold multiple positions and receive multiple salaries.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 12:23 am
CELKO (9/29/2011)
Learn a few basics, be polite and we will talk after you re-post
Pot, this is the kettle. I'd really like to discuss your current hue...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 3, 2011 at 11:45 am
Evil Kraig F (9/30/2011)
CELKO (9/29/2011)
Learn a few basics, be polite and we will talk after you re-postPot, this is the kettle. I'd really like to discuss your current hue...
Now THAT is hilarious! You should get an award!
Todd Fifield
October 3, 2011 at 12:42 pm
Sean Lange (9/29/2011)
ghanshyam.kundu (9/29/2011)
can you post your DDL properly with constraint of two tables. as per me your table design is not correct.you should have deptid in emp table as department is a higher entity then employee.
although post your DDL if we can help.
I would second what ghanshyam said. The table structure looks like it needs some serious work. You have an employeeID in the department table. This means you have a 1:1 relationship unless a single employee can hold multiple positions and receive multiple salaries.
agree.....if you are unsure of how to post table create scripts and data insert scripts, then pls post back
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply