我怎么能只找到较少的计数值名称必须要3 groupName也

Question

IN SQL (adventureWorks) Database…

I/P :
ALTER PROCEDURE spLessDep
AS
BEGIN
SELECT COUNT(Name) AS COUNT,max(GroupName)
FROM HumanResources.Department
WHERE GroupName <(SELECT MAX(GroupName) FROM HumanResources.Department)
GROUP BY GroupName
ORDER BY COUNT(Name)
END
EXEC spLessDep

O/p:

Name   GroupName
2      Inventory Management
2      Manufacturing
2      Quality Assurance
3      Research and Development
5      Executive General and Administration

要求:我想要所有的最小值列(名称)的唯一语句

Answer 1

我想你是在描述TOP (1) WITH TIES:

SELECT TOP (1) WITH TIES COUNT(Name) AS COUNT, GroupName
FROM HumanResources.Department
WHERE GroupName < (SELECT MAX(GroupName) FROM HumanResources.Department)
GROUP BY GroupName
ORDER BY COUNT(Name);