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)

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

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

FROM @Hold h
WHERE logoncount = (select max(logoncount) from @Hold h2 where h2.name = h.name )
| improve this answer | |

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).

| improve this answer | |

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

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.