Using the OVER Keyword - SQL Server

Notes¶

It is recommended NOT to use the OVER keyword within a SELECT statement containing a GROUP BY clause, as it frequently results in a syntax error and unpredictable results.

Example with the OVER Keyword¶

with MyData as (
    select
         SuperThemeId
        ,SuperThemeName
        ,ThemeId
        ,ThemeName
        ,Qty = count(1) over (partition by SuperThemeId)
    from 
        IncrThemeMapping    
    )
select * 
from MyData 
where Qty > 1

Example without the OVER Keyword¶

The following SQL statement is the equivalent of the previous one, but without using the OVER keyword. This is the syntax you would have to use prior to SQL Server 2005.

select
     SuperThemeId
    ,SuperThemeName
    ,ThemeId
    ,ThemeName
    ,Qty = (
        select count(1) 
        from IncrThemeMapping 
        where SuperThemeId = a.SuperThemeId
        )
from 
    IncrThemeMapping a
where 1=1
    and SuperThemeId in (
        select SuperThemeId 
        from IncrThemeMapping 
        group by SuperThemeId 
        having count(1) > 1
        )