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

Selecting Top Row In Each Group

RSS
Modified on Wed, Apr 07, 2010, 1:08 PM by Administrator Categorized as SQL Server
Consider a hypothetical view dbo.PeopleTest having three columns — DecadeBorn, FullName, and PersonId. For each DecadeBorn we want the FullName that occurs first (alphabetically) for that decade, along with the PersonId for that person.

with x as (
    select
         DecadeBorn
        ,FullName
        ,RowNumber = ROW_NUMBER() over (partition by DecadeBorn order by FullName)
    from
        dbo.PeopleTest
    )
select * 
from x 
where RowNumber = 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.