Using the OVER Keyword - SQL Server

Notes


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
        )