How to select first 10 elements from database using SQL?

Question

There is a query like SELECT * FROM clients ORDER BY id. I want to select only first 10 elements. How can I do this?

P.S. I'm using MySQL.

Answer 1

SELECT * FROM clients ORDER BY id LIMIT 10;

Answer 2

SELECT * FROM clients ORDER BY id Limit 10

Answer 3

Here's all you can do with a SELECT (taken from here):

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

So the statement you are looking for is:

SELECT * FROM clients ORDER BY id LIMIT 10

Answer 4

Note that OFFSET is very helpful to paginate:

LIMIT 10 OFFSET 11

for the second page of 10.

Answer 5

The MySQL way is to use

SELECT * FROM clients ORDER BY id LIMIT 10;

which is MySQL-specific. For a longtime there was no counterpart in other databases but the SQL:2008 standard introduces an additional syntax:

SELECT * FROM clients FETCH FIRST 10 ROWS ONLY;

And

SELECT * FROM clients OFFSET 1 FETCH NEXT 10 ROWS ONLY;

But the problem is, that this syntax isn't supported by MySQL and most other databases, yet. In case you care about portability you should follow the development there.

Please mind that you should always ORDER BY clauses else the result might be random on different calls.