All we know SQL Server 2005 has new features are going to discuss in this article is the Row Number function and Partitioning.
Normally developers used temporary tables and co-related sub-queries to generate calculated row numbers in a query. Now SQL Server 2005 provides a function, which replaces all of the additional resources we used to generate row numbers.
In a few word this means that you can horizontally partition the data in your table, thus deciding in which file group each rows must be placed.
This allows you to operate on a partition even with performance critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available; all the data in that partition are available for querying, even if the restore is not yet fully completed.
For reference:
http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx

In this article I am going to create a query which will return a selective data from a particular city

Scenario is; I wanted to display the only three records from each city.

Here is the example below.

Create Table #tempTable
( City varchar(255),
EmployeeName varchar(255)
)

Insert into #tempTable Values (’Alabama’,’Rana’)
go
Insert into #tempTable Values (’Alabama’,’John’)
go
Insert into #tempTable Values (’Alabama’,’Richard’)
go
Insert into #tempTable Values (’Alabama’,’Kash’)
go
Insert into #tempTable Values (’Alabama’,’Michele’)
go
Insert into #tempTable Values (’Alabama’,’Nicole’)
go
Insert into #tempTable Values (’Alaska’,’Nadia’)
go
Insert into #tempTable Values (’Alaska’,’Parker’)
go
Insert into #tempTable Values (’Alaska’,’Peter’)
go
Insert into #tempTable Values (’Alaska’,’Nash’)
go
Insert into #tempTable Values (’Alaska’,’Birds’)
go
Insert into #tempTable Values (’Missouri’,’Shani’)
go
Insert into #tempTable Values (’Missouri’,’Zeekh’)
go
Insert into #tempTable Values (’Missouri’,’Rashi’)
go
Insert into #tempTable Values (’Missouri’,’Sharon’)
go
Insert into #tempTable Values (’Missouri’,’Jack’)
go
Insert into #tempTable Values (’Missouri’,’Jerry’)
go

SELECT City,
EmployeeName,
Row,
FROM
(
SELECT City,
ROW_NUMBER() OVER (PARTITION BY City ORDER BY City asc) AS Row,
EmployeeName
From #tempTable
)
AS RowNumbers
WHERE Row > = 1 and Row