Return max from table

Transact-SQL
1

Hi,

How would I return the project that has the least number of employees working on it. If more than one project is min than return all the projects with that min value. In this case i will return projectids 2 and 3 has they both only have 1 employee working on them.
GO
/****** Object: Table [dbo].[project_employee] Script Date: 11/20/2015 21:19:45 /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project_employee]') AND type in (N'U'))
DROP TABLE [dbo].[project_employee]
GO
/ Object: Table [dbo].[Projects] Script Date: 11/20/2015 21:19:45 /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projects]') AND type in (N'U'))
DROP TABLE [dbo].[Projects]
GO
/ Object: Table [dbo].[Projects] Script Date: 11/20/2015 21:19:45 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projects]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Projects](
[Projectid] [int] IDENTITY(1,1) NOT NULL,
[ProjectName] nvarchar NULL,
CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED
(
[Projectid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[Projects] ON
INSERT [dbo].[Projects] ([Projectid], [ProjectName]) VALUES (1, N'ProjectA')
INSERT [dbo].[Projects] ([Projectid], [ProjectName]) VALUES (2, N'ProjectB')
INSERT [dbo].[Projects] ([Projectid], [ProjectName]) VALUES (3, N'ProjectC')
INSERT [dbo].[Projects] ([Projectid], [ProjectName]) VALUES (4, N'ProjectD')
SET IDENTITY_INSERT [dbo].[Projects] OFF
/ Object: Table [dbo].[project_employee] Script Date: 11/20/2015 21:19:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project_employee]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[project_employee](
[employeeid] [int] NOT NULL,
[projectid] [int] NOT NULL,
CONSTRAINT [PK_project_employee] PRIMARY KEY CLUSTERED
(
[employeeid] ASC,
[projectid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT [dbo].[project_employee] ([employeeid], [projectid]) VALUES (1, 1)
INSERT [dbo].[project_employee] ([employeeid], [projectid]) VALUES (1, 2)
INSERT [dbo].[project_employee] ([employeeid], [projectid]) VALUES (1, 3)
INSERT [dbo].[project_employee] ([employeeid], [projectid]) VALUES (2, 1)
INSERT [dbo].[project_employee] ([employeeid], [projectid]) VALUES (3, 1)

2

with ProjectByNumber as (select p.projectid,count(*) MinNumber from Projects p
join project_employee e
on p.[projectid]=e.projectid
group by p.projectid),

MinimumNumber as (select min(MinNumber) MinNumber from ProjectByNumber)

select p.projectid,p.MinNumber as NoOfProject from ProjectByNumber p
join MinimumNumber m
on p.MinNumber=m.MinNumber

3 
SELECT TOP (1) WITH TIES ProjectId, COUNT(*) AS EmployeeCount
FROM #project_employee
GROUP BY projectid
ORDER BY COUNT(*)
1 Like
4

Thanks both queries work great for me. ScottPletcher - I learned something new -didn't know about Ties :slight_smile: