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

Selecting Top Row In Each Group

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