Arctus Blog

In Microsoft SQL Server, you can use the keyword TOP to restrict the number of rows returned by your query:

SELECT TOP 10, *
    FROM Staff
    ORDER BY EmploymentStartDate;

In MySQL you can use the limit keyword:

SELECT *
    FROM Staff
    ORDER BY EmploymentStartDate
    LIMIT 10;

With Oracle, it looks as if the ROWNUM virtual column might do the trick, but sadly it doesn’t. That is derived from the position of a row in the physical database so doesn’t correlate with our ORDER. In fact, we actually need to use the row_number() function.

Row_number is a window function. It looks at the data after the where clause is applied and in an order that we specify using the keyword OVER. Unfortunately, this makes it unavailable to our WHERE clause.

Becuase we can’t use window functions in our where clauses, the following won’t work:

SELECT *, row_number() over (ORDER BY published_date desc) AS rownumber
    FROM Staff
    WHERE rownumber <= 10
    ORDER BY EmploymentStartDate;

Instead, we must select the order in a subquery. This is because the sub-query is executed before the outer query and thus the rownumber that we calculate is available in the WHERE clause (and also the ORDER BY).

Here is the working query, implemented as a sub-query on an inner join. Not that we need to join the ordered table to the original table, so an unique key field is required:

SELECT s.*, staff_order.rownumber
    FROM Staff s
    INNER JOIN (SELECT StaffId, row_number() over (ORDER BY EmploymentStartDate desc) AS rownumber FROM Staff) staff_order
    WHERE staff_order.rownumber <= 10
    ORDER BY staff_order.rownumber;

If you want to restrict the results (e.g. WHERE first name is something or other), you must add the WHERE to the sub-query. The outer query will automatically be filtered because we are INNER JOINing the sub-query on StaffId, so only matching StaffIds are there to be joined on.

This is far more complicated than with other database systems, but once you get it working it works well. Sub-queries are nasty, however, so here is the same code written well:

WITH staff_order AS (
    SELECT StaffId, row_number() over (ORDER BY EmploymentStartDate desc) AS rownumber
        FROM Staff
        WHERE Status = 1
)
SELECT s.*
    FROM Staff s
    INNER JOIN staff_order so
        ON s.StaffId = so.StaffId
    WHERE so.rownumber <= 10
    ORDER BY so.rownumber;

I hope you find this code useful!