Search This Blog & Web

Monday, July 27, 2009

Returning required rows when there is no primary key.

One of my friend ask solution for this problem

Problem : If a table has 11 records, how can we get records from record number 5 to record number 10 and there is no primary key in the table.

Solution: see the example in SQL Server in following example

Declare @Table Table(name varchar(50),DateCreated datetime)

Insert into @Table values ('Shamas',getdate())
Insert into @Table values ('Qamar',getdate())
Insert into @Table values ('Atif',getdate())
Insert into @Table values ('Kashif',getdate())
Insert into @Table values ('Maria',getdate())
Insert into @Table values ('Usman',getdate())
Insert into @Table values ('Wasif',getdate())
Insert into @Table values ('Narimaan',getdate())
Insert into @Table values ('Hina',getdate())
Insert into @Table values ('Saqib',getdate())
Insert into @Table values ('Farhan',getdate())

Select * from
(
Select row_number() over (order by name ) as rowid,
name,DateCreated
from @Table
) t
where rowid >= 5 and rowid < = 10

Summary: This query will assing row number to each record with name as order column. Then outer query will return record number as what you want.

We can do this in many other ways in SQL Server 2000 and 2008

1 comment:

Maria said...

exactly what i needed. thank u