Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Using the OVER Keyword - SQL Server

RSS
Modified on Sun, Oct 26, 2008, 2:04 AM by Administrator Categorized as 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
        )

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.