15,905,776 members
See more:
hi please any one tell me select 5th row in table with out enter row number how can i do it please tell me
Posted

## Solution 1

```WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,* FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 5```

Or:
```SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNum
FROM [Order Details]
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum = 5```

Bitto kumar 30-Nov-12 4:37am
sory i am asking with out enter row =5 is there any possibility
OriginalGriff 30-Nov-12 4:47am
If you want to return a single row from a table, then you have to identify it somehow, yes? If you want the fifth row and that is the only way you can identify it, then the number five is going to be involved at some point... :laugh:

Or is this a language problem, and you are not asking the question you think you are?
Bitto kumar 30-Nov-12 5:00am
no dont think wrong yesterday i faced this question in my interview so i asked. anyway thanks for your answer

## Solution 2

Here is one way to do it
SQL
```CREATE TABLE #SampleTable
(
ID INT,
[Name] VARCHAR(50)
)

INSERT INTO #SampleTable
SELECT 1, 'XYZ 1'UNION ALL
SELECT 2, 'XYZ 2' UNION ALL
SELECT 3, 'XYZ 3' UNION ALL
SELECT 4, 'XYZ 4' UNION ALL
SELECT 5, 'XYZ 5' UNION ALL
SELECT 6, 'XYZ 6' UNION ALL
SELECT 7, 'XYZ 7' UNION ALL
SELECT 8, 'XYZ 8'

SELECT TOP 1 * FROM
(
SELECT TOP 5 * FROM
(
SELECT TOP 5 * FROM #SampleTable
ORDER BY Id ASC
) T
ORDER BY Id DESC
) T1

DROP TABLE #SampleTable```

v2