Select FIRST/TOP N rows in MS SQL, Oracle and DB2
How do you select the first N rows in various databases?
A quick thing to remember is that rows are not stored in any particular sequence in a database. They can be placed in random data blocks in the database without any order whatsoever. If a query returns X rows, they definitely do not mean that these rows are stored in exactly this same order. It is just that these rows have satisfied your select query and are being displayed in the order they are found (unless you specify an ORDER BY clause). So how do I obtain only the first N rows from a query which actually returns more than N rows?
Here is a quick overview of how it can be done.
MS SQL
SELECT TOP 10 name, description
FROM TABLEA
ORDER BY name
The above query will return the first 10 rows sorted by name. How do I get the bottom 10 rows? Use the DESC keyword in the ORDER BY clause and it will sort it in reverse order.
ORACLE
SELECT name, description
FROM TABLEA
WHERE rownum <=10
ORDER BY NAME
One has to be careful when trying a similar thing in ORACLE. The above code will not work, simply because the query will return the first 10 rows and then order them, which is definitely not what you wanted. ROWNUM is a pseudo column that is assigned values based on the results returned. So the above code would have worked if there was no ORDER BY clause. The right way would be:
SELECT * FROM
(SELECT name, description
FROM TABLEA
ORDER BY NAME)
WHERE rownum <=10
A good resource for various questions on Oracle is:
Ссылка скрыта от гостей
DB2
SELECT name, description
FROM TABLEA
ORDER BY NAME
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS
