CODEWITHSUNDEEP
sqlmysql
Sergey
Sergey

Reputation: 49728

How to select first 10 elements from database using SQL?

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.

Upvotes: 12

Views: 31305

Answers (5)

johannes
johannes

Reputation: 15969

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.

Upvotes: 0

Seyed Reza Dadrezaei
Seyed Reza Dadrezaei

Reputation: 583

try this:

SELECT TOP 10 * FROM clients ORDER BY id

Upvotes: 6

David W
David W

Reputation: 945

Note that OFFSET is very helpful to paginate:

LIMIT 10 OFFSET 11

for the second page of 10.

Upvotes: 5

tangens
tangens

Reputation: 39733

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

Upvotes: 5

Luke Bayes
Luke Bayes

Reputation: 3284

SELECT * FROM clients ORDER BY id LIMIT 10;

Upvotes: 23

Related Questions