How do I use where Max 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)
sql-server
To use where Max SQL, 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).