Someone asked me if there was a cross-vendor way of doing LIMIT/TOP. I said that there was none.
Thinking about this question a little more I have to conclude I was probably wrong with my answer (to a degree).
The SQL standard has 'FETCH FIRST n ROWS' for this but that is (still) not supported by all database vendors.
But you could do it using a Window Function and as far as I know only MySQL doesn't support these (yet?).
But let me explain the situation first.
SQL Server and Sybase both use the syntax 'SELECT TOP 5 myColumn, otherColumn FROM myTable' to get the first 5 rows to the result set.
PostgreSQL, MySQL use 'SELECT myColumn, otherColumn FROM myTable LIMIT 5'. Oracle however has a special-internal column named rownum that you can use in a WHERE clause.
Now most current database versions do support the row_number() window function so we could use this.
- Code: Select all
SELECT myColumn,
otherColumn,
row_number() OVER (ORDER BY myPK) AS rownr
FROM myTable
This will return 3 columns where rownr is an alias for the window function.
However to filter on it we would have to use that full function in the WHERE clause which is not allowed (you get an error that window functions can not be used in the WHERE clause).
So we have to wrap this query into another to be able to filter on the rownr like so:
- Code: Select all
SELECT myColumn, otherColumn FROM (
SELECT myColumn,
otherColumn,
row_number() OVER (ORDER BY myPK) AS rownr
FROM myTable
) t
WHERE rownr <= 5
This will give us the first 5 rows of the result set.
Now if we want to get rows 6 to 10 (i.e. using an OFFSET) to do some paging through the result set we can use the same approach like so:
- Code: Select all
SELECT myColumn, otherColumn FROM (
SELECT myColumn,
otherColumn,
row_number() OVER (ORDER BY myPK) AS rownr
FROM myTable
) t
WHERE rownr >= 6 and rownr <= 10
Or you can use the BETWEEN syntax as well:
- Code: Select all
SELECT myColumn, otherColumn FROM (
SELECT myColumn,
otherColumn,
row_number() OVER (ORDER BY myPK) AS rownr
FROM myTable
) t
WHERE rownr BETWEEN 6 AND 10
There is however one important thing to keep in mind.
ALWAYS use the same ORDER BY in the row_number() Window Function as you use on the main query or else you can get unexpected results.
Hope this helps.
