SQL Server : return only max from count per day-CodePudding

The following query returns: multiple dates (with count for each user per day), IP count, username.

I need it to return only max values for user (only the highest one) per day.

If I remove the username from the group by, it works just fine. The problem is that I also need the username in the table results.

I tried using a sub query with no lock.

Any help would be appreciated.

SELECT DISTINCT 
    FORMAT([UTCTimestamp], 'yyyy-MM-dd') AS 'DATE',
    T.Username,
    COUNT(clientIP) AS "CountClientIP" 
FROM
    dbo.tablename O
LEFT JOIN
    [DBNAME2]..vwAD_tablename T ON T.UserID = O.userID
LEFT JOIN
    [DBNAME1]..Event E ON E.Code = O.Code  
WHERE 
    FORMAT([UTCTimestamp], 'yyyy-MM-dd') LIKE '2018-01-%' 
    AND T.Username IS NOT NULL
GROUP BY   
    T.Username, FORMAT([UTCTimestamp], 'yyyy-MM-dd')
ORDER BY 
    FORMAT([UTCTimestamp], 'yyyy-MM-dd'), COUNT(clientIP) DESC

CodePudding user response：

Add a ROW_NUMBER, wrap it in a sub-query, then filter on it.

SELECT [DATE], Username, CountClientIP
FROM
(
    SELECT  
        FORMAT([UTCTimestamp],'yyyy-MM-dd') AS [DATE],
        T.Username,
        COUNT(DISTINCT clientIP) AS [CountClientIP], 
        ROW_NUMBER() OVER (PARTITION BY FORMAT([UTCTimestamp],'yyyy-MM-dd')
                           ORDER BY COUNT(DISTINCT clientIP) DESC) AS rn
    FROM [DBNAME].dbo.tablename O
    LEFT JOIN [DBNAME2]..[vwAD_tablename] T ON T.UserID = O.userID
    LEFT JOIN [DBNAME1]..[Event] E ON E.Code = O.Code  
    WHERE FORMAT([UTCTimestamp],'yyyy-MM-dd') LIKE '2018-01-%' 
      AND T.Username IS NOT NULL
    GROUP BY T.Username, FORMAT([UTCTimestamp],'yyyy-MM-dd')
) q
WHERE rn = 1
ORDER BY [DATE]