How To Retrieve TOP-N Rows from a Table in SQL?

Retrieving TOP-N Rows in Sybase Database

The Sybase database provides the top syntax that can be used in SQL select queries to limit the records returned from a query. This is especially useful when querying very large tables in cases where the user only cares about a subset of the records. Listed below is an example of how to use the top syntax for Sybase.

SELECT top 10 * FROM employee order by salary desc;

Retrieving TOP-N Rows in PostgreSQL

PostgreSQL provides a mechanism for limiting query results using the limit and / or offset SQL syntax. You can use the the following SQL statement to retrieve the top-n rows in a table:

SELECT * FROM employee order by salary desc limit 10;

In the above statement, the query will return the first 10 employees with the highest salary.

The offset feature in PostgreSQL will let you return an arbitrary range of rows (eg return rows 10-20). This is very handy for displaying pages of records:

SELECT * FROM employee order by salary desc limit 10 offset 20;