selecting specific rows from database in sql

Question

Possible Duplicate:
How to select the nth row in a SQL database table?

I have a table that I want to select Select 1 record and in other command I want to select second row and in other command 3th row and ... how can I select for example 4th record from top of table without having the row number . just I want to select 4th row from top of table . how can I do this ?

Answer 1

you can select the 4th row by this code in MS sql server.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY DayRangeId ASC) AS rownumber,
    DayRangeId
  FROM DayRangeTable
)  as temptablename
WHERE rownumber = 4

Answer 2

You want the first row, then the second row, then the third row and then the fourth row?

Can't you select the top n rows, and itterate through them?

I'm not sure what you mean by without having the row number though. Do you mean "Select the 4th row without knowing I need the 4th row"???

If you really need a piece of SQL that can select a specific row...

;WITH
  sequenced_data AS (
    SELECT ROW_NUMBER() OVER (ORDER BY <whatever fields>) AS row_id, * FROM myTable
)
SELECT
  TOP 1 *
FROM
  sequenced_data
WHERE
  row_id = @n