GROUP BY Question

Given the following tables, I would like to figure out employee (employeeID)
leads the most assignments.
Doing it in two steps works (after a fashion) with a view (as listed below)
but I would like the Assignmentleader ID and the number of assignments that
person leads only for the leader that leads the most assignments and do it
in one query if possible.
CREATE VIEW MostNum AS
SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
FROM Assignments
GROUP BY AssignmentLeader
CREATE TABLE Employees
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName CHAR (20) NULL,
LastName CHAR (20) NULL,
HireDate DATETIME NULL,
TerminationDate DATETIME NULL,
EmployeeComments CHAR (300) NULL,
SupervisorsID INT NULL,
FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
)
--Employees sample data
INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
00:00:00.000',NULL,'The Big Boss',NULL)
INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
00:00:00.000',NULL,'VP',101)
INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
00:00:00.000',NULL,'Accounting Supervisor',101)
INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
00:00:00.000',NULL,'Finance Supervisor',101)
INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
00:00:00.000',NULL,'MIS Support',101)
INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
00:00:00.000',NULL,'Shipping Supplier',101)
INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
00:00:00.000',NULL,'Shipping PC',106)
INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
00:00:00.000',NULL,'Secretary',103)
INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
00:00:00.000',NULL,'Warehouse PC',101)
CREATE TABLE Assignments
(
AssignmentID INT NOT NULL PRIMARY KEY,
AssignmentDescription CHAR(60) NULL ,
AssignmentLeader INT NULL
FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
)
--Assignments sample data
INSERT INTO Assignments VALUES(1,'Run operations of company',105)
INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark V',101)
INSERT INTO Assignments VALUES(5,'Research and develop a new pump
sprayer',102)
INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)SELECT TOP 1 EmployeeID, COUNT(*) FROM Assignments GROUP BY EmployeeID ORDER
BY 2 DESC
"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>|||"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>
Transact-SQL proprietary version:
SELECT TOP 1 WITH TIES
assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
ORDER BY COUNT(*) DESC ;
ANSI/ISO Standard SQL version:
SELECT assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
HAVING COUNT(*)>= ALL
(SELECT COUNT(*)
FROM Assignments
GROUP BY assignmentleader);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23hw5rz7QGHA.1096@.TK2MSFTNGP11.phx.gbl...
> "David Olsen" <david.olsen@.usu.edu> wrote in message
> news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Transact-SQL proprietary version:
> SELECT TOP 1 WITH TIES
> assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> ORDER BY COUNT(*) DESC ;
>
> ANSI/ISO Standard SQL version:
> SELECT assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> HAVING COUNT(*)>= ALL
> (SELECT COUNT(*)
> FROM Assignments
> GROUP BY assignmentleader);
>
Thanks dude (and Aaron), you guys rock.

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>