Top vs Rownum

I guess talking about Sql queries these days, when Non Relational DBs are all the rage, is quite "uncool", but anyway...

For someone that has mainly worked with SqlServer, that fact that Oracle misses the TOP clause is quite shocking. You have to resort to using the ROWNUM pseudocolumn.

So a query like this:

SELECT TOP 10 * FROM cities

has to be written like this for Oracle:

SELECT * FROM cities WHERE ROWNUM <= 10;

It's odd, as I would say almost any other RDBMS out there implements TOP, but well, it's not a big deal. However, there is a huge difference when combined with ORDER By. If you rewrite this query:

SELECT TOP 10 * FROM cities ORDER BY population

like this:

SELECT * FROM cities WHERE ROWNUM <= 10 ORDER By population

You'll have a problem. In the first query the TOP clause to limit the number of results is executed after the ORDER BY, but in the second query, the WHERE ROWNUM condition is executed before the ORDER BY. This means that you will get whatever first 10 rows you have in your table, and then you'll order only those restricted rows, so obviously that is not what we want.

Hopefully the solution is pretty simple. In order to execute first the ORDER BY and then the filtering, you can use a composed query like this:

SELECT * FROM (
SELECT * FROM cities ORDER By population
)
WHERE ROWNUM <= 10

I came to this simple solution on my own, and doing some search to see if there is another better option, it seems like it's the common solution.