Getting Specific Row in SQL 2000 & 2005

Here is the query to find the specific row in Sql 2000

SELECT ( SELECT SUM(1)

FROM [Table_Name]
WHERE [TableID]<=reg.[TableID]
) AS Row
,*
FROM
[Table_Name] AS reg
WHERE ( SELECT SUM(1)

FROM [Table_Name]
WHERE [TableID]<=reg.[TableID]
) = 5

Note: 5 is the row number you want to find in the table.

Now for SQL 2005

SELECT us.TableID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Table_Name].[TableID]) AS Row,
[Table_Name].[TableID]
FROM [Table_Name] ) us
WHERE Row = 5

In this built in function ROW_NUMBER is used. Again '5' is the row number you are looking for. u can change it with the row number u want to find.

Read the rest of this entry »