• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

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?
0
r3nder
Asked:
r3nder
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>count the max id
So if your data set contains 1, 2, 3, 4, 5, 5, 5 then the 'count the max id' = 3, since there's three fives?

Give this a whirl..
SELECT COUNT(max_id) 
FROM ToolAssemblies
WHERE max_id IN (Select MAX(ID)  max_id from  ToolAssemblies WHERE ToolNumber =52 )

Open in new window

0
 
r3nderAuthor Commented:
got the idea - thanks Jim

SELECT COUNT(ID)
FROM ToolAssemblies
WHERE id IN (Select MAX(ID)as max_id from  ToolAssemblies WHERE ToolNumber =52 )
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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 )

Open in new window

Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now