SQL Server – Use MAX() in WHERE Clause

sql serversql-server-2008-r2t-sql

I am attempting to use MAX() in my WHERE clause but I am getting an error of

Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

This is the output I expect from working syntax

Blue - 3
green - 0
orange - 1
pink - 1
black - 0
white - 2

Here is sample table structure and the query I tried – how can I successfully use MAX() in the WHERE clause?

Declare @Hold Table 
(
  id int NOT NULL IDENTITY (1,1), 
  name varchar(500), 
  logoncount int
)
Insert Into @HOld (name, logoncount) Values
('blue', 1),('blue', 2), ('blue', 3)
,('green', NULL), ('orange', 1), ('pink', 1)
,('black', NULL), ('white', 1), ('white', 2)

Select 
*
FROM @Hold
WHERE logoncount = (select max(logoncount) from @Hold)

Best Answer

What you're looking for is probably something like this. 

Select 
*
FROM @Hold h
WHERE logoncount = (select max(logoncount) from @Hold h2 where h2.name = h.name )
Related Question