Use MAX() In Where Clause

Question

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)

Answer 1

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 )

Answer 2

Subjective, but I prefer this approach... you only have to reference the table once, and you have a lot more flexibility for handling ties or determining which tie wins.

;WITH x(id, name, logoncount, rn) AS 
(
  SELECT id, name, COALESCE(logoncount,0), ROW_NUMBER() OVER 
    -- if you want ties, use DENSE_RANK() instead of ROW_NUMBER()
    (
      PARTITION BY name ORDER BY logoncount DESC
      -- if you don't want ties, add other columns for further tie-breaking
    )
  FROM @Hold
)
SELECT id, name, logoncount FROM x WHERE rn = 1;

If you later decide you don't want NULLs, you can simply filter them out in the inner query (FROM @hold WHERE logoncount IS NOT NULL).

Answer 3

You can use (fixed form of query from other answer, which return data as expected):

Select 
name, coalesce(logoncount,0) as logoncount
FROM Test h
WHERE ISNULL(logoncount,0) = (select ISNULL(max(logoncount),0) from Test h2 where h2.name = h.name )

or use my variant - shorter form:

SELECT name, COALESCE(MAX(logoncount),0) as logoncount FROM Test GROUP BY name

more clean, much shorter