How to limit the results of a SQL query

Question

I am wondering if it is possible to limit the result of a SQL query?

For example, return only up to 50 rows from:

SELECT * FROM <table> 

thanks.

Answer 1

Yes it is possible. This is different from db engines.

Postgres:

 SELECT * FROM <table> LIMIT 50 

SQL Server:

 SELECT TOP 50 * FROM <table> 

...

Answer 2

You can use the TOP sentence :

 SELECT TOP 50 * FROM <table> 

If your database does not support it, you can also try LIMIT and ROWNUM , but once again it will depend on the database you are using.

Answer 3

Yes, of course, perhaps in MYSQL:

The LIMIT clause can be used to limit the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be non-negative integer constants (except when prepared statements are used).

With two arguments, the first argument indicates the offset of the first row to return, and the second indicates the maximum number of rows to return. The start line offset is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To get all rows from a specific offset to the end of the result set, you can use some large number for the second parameter. This statement extracts all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

Using one argument, the value indicates the number of rows returned from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Answer 4

In MySQL you should use the LIMIT clause

  SELECT * FROM <table> LIMIT 50 

Answer 5

SQL standard

As I explained in this article , the SQL: 2008 standard provides the following syntax to limit the SQL result set:

 SELECT title FROM post ORDER BY id DESC FETCH FIRST 50 ROWS ONLY 

The SQL-2008 Top-N records offer is supported in Oracle since 12c, SQL Server since 2012, and PostgreSQL since 8.4.

SQL Server

Although SQL Server supports the standard SQL: 2008 Top-N syntax, you must also specify the OFFSET clause:

 SELECT title FROM post ORDER BY id DESC OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY 

In older versions of SQL Server, you can use TOP:

 SELECT TOP 50 title FROM post ORDER BY id DESC 

Oracle 11g and earlier

Prior to version 12c, to obtain Top-N records, it was necessary to use a view and the ROWNUM pseudo-column:

 SELECT * FROM ( SELECT title FROM post ORDER BY id DESC ) WHERE ROWNUM <= 50 

MySQL and PostgreSQL 8.3 or later

Traditionally, MySQL and PostgreSQL use the LIMIT clause to restrict the result set to Top-N entries:

 SELECT title FROM post ORDER BY id DESC LIMIT 50