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>
