Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL TOP Clause (SQL ROWNUM - SQL LIMIT)


SQL TOP clause fetching only top Nth number of record or Nth percentage record result set return. But 10g onward TOP clause no longer supported replace with ROWNUM clause.

Nth number/percentage of record user define, user to specified number/percentage of records return.

SQL TOP clause use always with SELECT statement. When you SELECT statement to select records, but you need only first 3 records you just use TOP clause to return only first 3 records.


Syntax :

Considering following syntax that help you to understanding TOP clause,

SELECT [ TOP number | percentage ]
	column_name1, column_name2, ...
	FROM table_name;

Example Table :

We have following employee_hour table that store weekday hours for each employee:

SQL> SELECT * FROM employee_hour;

NAME                 DAY             HOURS
-------------------- ---------- ----------
Opal Kole            Monday              8
Max Miller           Monday              8
Beccaa Moss          Monday              8
Paul Singh           Monday              9
Opal Kole            Tuesday             9
Max Miller           Tuesday             6
Beccaa Moss          Tuesday            10
Paul Singh           Tuesday             8
Opal Kole            Wednesday           7
Max Miller           Wednesday           9
Beccaa Moss          Wednesday          11
Paul Singh           Wednesday          12

12 rows selected.

Run it...   »

Example Statement :

SQL> SELECT TOP 3 * 
	FROM employee_hour 
	ORDER BY hours DESC;

NAME                 DAY             HOURS
-------------------- ---------- ----------
Paul Singh           Wednesday          12
Beccaa Moss          Wednesday          11
Beccaa Moss          Tuesday            10

3 rows selected.

I'm not sure this is working or not but I was finding TOP clause work on Oracle 8i, 9i. And now TOP clause replace with ROWNUM clause to fetch number of rows.


SQL ROWNUM Clause (10g onward)

Syntax :

Considering following syntax that help you to understanding ROWNUM clause,

SELECT
	column_name1, column_name2, ...
	FROM table_name
	WHERE ROWNUM operator condition;

Example Statement :

SQL> SELECT * 
	FROM employee_hour 
	WHERE ROWNUM <= 3;

NAME                 DAY             HOURS
-------------------- ---------- ----------
Opal Kole            Monday              8
Max Miller           Monday              8
Beccaa Moss          Monday              8

Run it...   »

ROWNUM clause to fetching only top 3 records.


Example Statement :

SQL> SELECT * 
	FROM ( SELECT * FROM employee_hour ORDER BY hours DESC)
	WHERE ROWNUM <= 3;

NAME                 DAY             HOURS
-------------------- ---------- ----------
Paul Singh           Wednesday          12
Beccaa Moss          Wednesday          11
Beccaa Moss          Tuesday            10

Run it...   »

ROWNUM clause use with sub query to fetching record hours descending order and filter top 3 records.