Friday, August 30, 2019

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>