Max no of emp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Max no of emp

Author

Topic

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-05-04 : 08:02:18

Hi

I have 2 tables, I need to find which dept have max no emp?

Empmaster

Empname deptid
a 1
b 2
c 1
d 1

Dept

Deptid Deptname
1 s/w
2 H/W

Expected O/p

1 S/w

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-04 : 08:22:13

Do you mean this?


select d.Deptid,d.Deptname
from Dept d
join
(select top 1 deptid,count(*) as Counter
 from Empmaster
 group by deptid
 order by Counter desc) m
on m.deptid=d.deptid

Webfred
Edit: Alias in Select-List

No, you're never too old to Yak'n'Roll if you're too young to die.

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2009-05-04 : 08:35:55

select top 1 depid,deptname from Empmaster
inner join dept on Empmaster.depid=dept.deptid
group by depid,deptname
order by count(deptid) desc

malay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-04 : 09:25:01

quote:
Originally posted by malaytech2008

select top 1 depid,deptname from Empmaster
inner join dept on Empmaster.depid=dept.deptid
group by depid,deptname
order by count(deptid) desc

malay

That looks not accomplishable...

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-04 : 11:10:05

quote:
Originally posted by webfred

quote:
Originally posted by malaytech2008

select top 1 depid,deptname from Empmaster
inner join dept on Empmaster.depid=dept.deptid
group by depid,deptname
order by count(deptid) desc

malay

That looks not accomplishable...

No, you're never too old to Yak'n'Roll if you're too young to die.

Maybe he meant something like this:

DECLARE @employee TABLE (
	[Empname] NVARCHAR(255)
	, [deptid] INT
	)
INSERT  @employee 
      SELECT 'a', 1
UNION SELECT 'b', 2
UNION SELECT 'c', 1
UNION SELECT 'd', 1

DECLARE @dept TABLE (
	[Deptid] INT
	, [Deptname] NVARCHAR(255)
	)
INSERT @dept
      SELECT 1, 'S/W'
UNION SELECT 2, 'H/W'


SELECT TOP (1)
	d.[deptID] AS [departmentId]
	, d.[deptname] AS [department]
FROM
	@dept d
	JOIN @employee e ON e.[deptId] = d.[deptId]
GROUP BY
	d.[deptId]
	, d.[deptname]
ORDER BY
	COUNT(e.[empname]) DESC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-04 : 11:26:35

Yes you are right. I was sure he meant something like that.
But it is not the right way to post a solution that needs a revision like that...

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-05-06 : 11:47:00

I will make like this..

select top 1 * from
(Select d.deptid,d.deptname,count(*) as counter from employee e, dept d where e.deptid=d.deptid
group by d.deptid,d.deptname) as mytable

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 02:41:07

quote:
Originally posted by senthil_nagore

I will make like this..

select top 1 * from
(Select d.deptid,d.deptname,count(*) as counter from employee e, dept d where e.deptid=d.deptid
group by d.deptid,d.deptname) as mytable

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Top 1 is meaningless and give you incorrect result until you use Order by clause. Why do you want to prefer yours instead of other suggestions?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources