开发者

How to select first 10 elements from database using SQL?

开发者 https://www.devze.com 2022-12-10 07:36 出处:网络
There is a query like SELECT * FROM clients ORDE开发者_开发知识库R BY id. I want to select only first 10 elements. How can I do this?

There is a query like SELECT * FROM clients ORDE开发者_开发知识库R BY id. I want to select only first 10 elements. How can I do this?

P.S. I'm using MySQL.


SELECT * FROM clients ORDER BY id LIMIT 10;


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


Note that OFFSET is very helpful to paginate:

LIMIT 10 OFFSET 11

for the second page of 10.


try this:

SELECT TOP 10 * FROM clients ORDER BY id


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消