How to Skip N Rows in an Oracle SELECT Statement

I saw this question on a SQL Server blog. Apparently it was asked at an interview. Here is the suggested answer:

SELECT *

FROM [AdventureWorks2014].[Person].[Address]

ORDER BY AddressID

OFFSET 100 ROWS;

 

I don’t have access to SQL Server right now so I wondered how I might do this in Oracle. This is what I came up with. I ran it in an Oracle 11.2.0.4 database:

SQL> l

  1   select username from dba_users

  2   minus

  3   (select username from

  4    (select username from dba_users order by username)

  5    where rownum <=10)

  6* order by username

SQL> /

 

USERNAME

------------------------------

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

22 rows selected.

 

SQL>

 

It skips the first 10 rows from DBA_USERS and returns the rest. Here are all the rows, in case you are interested:

 

SQL> select username from dba_users order by 1

  2  /

 

USERNAME

------------------------------

ABCD

ANONYMOUS

APEX_030200

APEX_PUBLIC_USER

APPQOSSYS

CTXSYS

DBSNMP

DIP

EXFSYS

FLOWS_FILES

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

32 rows selected.

 

SQL>