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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<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 )
ASKER
SELECT COUNT(ID)
FROM ToolAssemblies
WHERE id IN (Select MAX(ID)as max_id from ToolAssemblies WHERE ToolNumber =52 )