I am trying to count number of columns contain specific text based on multiple WHERE LIKE condition for MAX value only.

I want to COUNT following (All rows WHERE Names is Like Al and Text is New only or rows which has the max value in this example value of 3): The results for the above table should be 2 as there are two lines which meets the criteria.

SELECT COUNT(Text) AS NA WHERE Name LIKE 'AL' AND Text LIKE 'NA' AND Max(Value)

I know the above query is totally wrong but I am trying to explain what I am looking for.

CodePudding user response：

You need a subquery for the max value:

select count(*)
from mytable
where name = 'Al' and text = 'NA' and value = (select max(value) from mytable);

CodePudding user response：

Perhaps a CTE with DENSE_RANK and then a COUNT?

WITH CTE AS(
    SELECT ID,
           Name,
           Text,
           Value,
           DENSE_RANK() OVER (PARTITION BY Name ORDER BY [Value] DESC) AS DR
    FROM(VALUES(1,'Al','New',1),
               (2,'Al','NA',2),
               (3,'Al','NA',3),
               (4,'Al','NA',3),
               (5,'BK','New',1),
               (9,'BK','Old',2),
               (7,'BK','NA',3))V(ID,Name,Text,Value)
    WHERE Name = 'Al'
      AND Text = 'NA')
SELECT COUNT(*)
FROM CTE
WHERE DR = 1;