asked on select and count the max id
I have a query where I am trying to count the max id that meets this criteria
Select Count(ID),MAX(ID) from ToolAssemblies WHERE ToolNumber =52
This should come up as 1 but it counts 29?
Select Count(ID),MAX(ID) from ToolAssemblies WHERE ToolNumber =52
This should come up as 1 but it counts 29?
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
Jim Horn
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
<slight cleanup, don't need an alias with an IN clause>
SELECT COUNT(id)
FROM ToolAssemblies
WHERE id IN (Select MAX(ID) from ToolAssemblies WHERE ToolNumber = 52 )
SELECT COUNT(ID)
FROM ToolAssemblies
WHERE id IN (Select MAX(ID)as max_id from ToolAssemblies WHERE ToolNumber =52 )