FETCH FIRST and FETCH NEXT do exactly the same thing. The reason both exist because of the mandatory preceding OFFSET keyword. Using the word FIRST combined with OFFSET can be confusing to a human reader:
CopySELECT *
FROM Foo
ORDER BY ID
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY; -- Does this mean rows 6 to 10, or 1 to 5?
Whereas:
CopySELECT *
FROM Foo
ORDER BY ID
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY; -- Now it's clear!
SqlFiddle example here.
Similarly, you can use ROW and ROWS interchangeably, even if it breaks English grammar norms.
sql - What is the difference between fetch Next and fetch First in the Order By [...] OFFSET [..] FETCH [...] clause? - Stack Overflow
SQL - Select first 10 rows only? - Stack Overflow
SQL Server 'FETCH FIRST 1 ROWS ONLY' Invalid usage - Stack Overflow
sql - Is there a difference between 'FETCH' and 'FETCH FIRST'? - Stack Overflow
Q1. How to select the first 5 rows in SQL?
Q3. What is ORDER BY in SQL?
Q4. How to remove duplicates in SQL?
Videos
FETCH FIRST and FETCH NEXT do exactly the same thing. The reason both exist because of the mandatory preceding OFFSET keyword. Using the word FIRST combined with OFFSET can be confusing to a human reader:
CopySELECT *
FROM Foo
ORDER BY ID
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY; -- Does this mean rows 6 to 10, or 1 to 5?
Whereas:
CopySELECT *
FROM Foo
ORDER BY ID
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY; -- Now it's clear!
SqlFiddle example here.
Similarly, you can use ROW and ROWS interchangeably, even if it breaks English grammar norms.
There is no difference. The SQL standard simply allows both, maybe to bridge differences between syntaxes, or to allow you the choice to write 'fluent English' queries.
This similar as to why the standard allows you to write:
Copyfetch first row
fetch first rows
fetch first 1 row
fetch first 1 rows
(and variants with next) which will all fetch a single row. That this also allows you to write grammatically incorrect sentences like fetch first 2 row is taken for granted.
In SQL Server, use:
select top 10 ...
e.g.
select top 10 * from myTable
select top 10 colA, colB from myTable
In MySQL, use:
select ... order by num desc limit 10
Depends on your RDBMS
MS SQL Server
SELECT TOP 10 ...
MySQL and PostgreSQL 8.3 or older:
SELECT ... LIMIT 10
Sybase (>v15 ASE support TOP too)
SET ROWCOUNT 10
SELECT ...
Etc.
Sometimes it's better to try things out before you ask.
SQL> with basis as (select level l,100 - level lm from dual connect by level <=100)
2 select l, lm from basis order by lm fetch first 5 percent rows only;
L LM
---------- ----------
100 0
99 1
98 2
97 3
96 4
So the first statement works.
Second one:
SQL> with basis as (select level l, 100 - level lm from dual connect by level <=100)
2 select l, lm from basis order by lm fetch 5 percent rows only;
Error starting at line : 6 in command -
with basis as (select level l, 100 - level lm from dual connect by level <=100)
select l, lm from basis order by lm fetch 5 percent rows only
Error at Command Line : 7 Column : 48
Error report -
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
So error message points out that after fetch something is missing. Or as pointed out by a_horse. Read the manual.
Your exam is wrong, the correct query is the top one.
The FETCH statement is a PL/SQL statement that is used to retrieve rows of data from the result set of a multi-row query, and as such, cannot be used in a SQL query : https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems020.htm
The FETCH (NEXT | FIRST) statement is used in queries to retrieve only a certain number or rows from a result : https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html