-3

Let us take a table t1

SQL > Select * from t1;

COL1

    9
    1
    8
    6
    4

Q) Query to retrieve third row from the table.

A) Oracle : 

SQL > select col1 from (select col1, row_number() over(order by rowid) rn from t1) where rn=3;

As rowid psuedo column doesn't exist in other databases, how can retrieve a nth record from a table in databases like MYSQL, SQL Server etc.

rama krishna
  • 21
  • 2
  • Select * from t1 LIMIT n,1; where n = 3 for third row – khan Farman Oct 23 '18 at 09:35
  • 1
    You can't find a syntax for this supported by all of MySQL, Oracle and SQL Server. – jarlh Oct 23 '18 at 09:36
  • Short answer, don't do this as a relational database table is logically an unordered set of rows, That being said, one could order by the physical location pseudo column in SQL Server using a CTE: `WITH a AS ( SELECT col1, ROW_NUMBER() OVER(ORDER BY %%physloc%%) rn FROM t1 ) SELECT col1 FROM a where rn=3;` – Dan Guzman Oct 23 '18 at 09:49

2 Answers2

0

SQL Server also, you have the ROW_NUMBER Function

You Can Go like

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(ORDER BY EmployeeID),
        *
        FROM dbo.Employee
)
SELECT
    *
    FROM CTE
        WHERE RN = 5--To Get the 5th Record
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
0

for mssql you can use row_number() window funciton

select * from 
(
select *, row_number() over(order by col1) rn from t
) t1 where t1.rn=1 -- 2 or 3, n
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63