Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses The error in the title is produced when add the second condition in my where clause. I understand the error, have tried using subqueries as seen in other posts to fix this but no luck. Does anyone know how I can filter row_ID = 1 successfully?

Here is my query:

SELECT
    a.database_name ,
    a.collection_time ,
    ROW_NUMBER() OVER ( 
        PARTITION BY 
            CONVERT(DATE, SWITCHOFFSET(
                CAST (a.collection_time AS DATETIMEOFFSET), '+00:00')) 
        ORDER BY a.collection_time DESC) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE
    a.database_name = 'MDWDB'
    AND ROW_NUMBER() OVER ( 
            PARTITION BY 
                CONVERT(DATE, SWITCHOFFSET(
                    CAST (a.collection_time AS DATETIMEOFFSET), '+00:00')) 
            ORDER BY a.collection_time DESC) = 1
One method is with a common table expression like the example below.  Note that the preceding statement must be terminated with a semicolon.

WITH cte AS (
SELECT  a.database_name ,
        a.collection_time ,
        ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                                              '+00:00')) ORDER BY a.collection_time DESC ) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE   a.database_name = 'MDWDB'
    )
SELECT database_name
    ,collection_time
    ,row_ID
FROM cte
WHERE Row_ID = 1;