Click here to Skip to main content
15,905,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
 
Share this answer
 
Comments
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
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
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900