MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL Limit and Offset

From WikiOD

Syntax[edit | edit source]

  • SELECT column_1 [, column_2 ]

FROM table_1

ORDER BY order_column

LIMIT row_count [OFFSET row_offset]

  • SELECT column_1 [, column_2 ]

FROM table_1

ORDER BY order_column

LIMIT [row_offset,] row_count

Remarks[edit | edit source]

"Limit" could mean "Max number of rows in a table".

"Offset" mean pick from row number (not to be confused by primary key value or any field data value)

Limit and Offset relationship[edit | edit source]

Considering the following users table:

id username
1 User1
2 User2
3 User3
4 User4
5 User5

In order to constrain the number of rows in the result set of a SELECT query, the LIMIT clause can be used together with one or two positive integers as arguments (zero included).

LIMIT clause with one argument[edit | edit source]

When one argument is used, the result set will only be constrained to the number specified in the following manner:

SELECT * FROM users ORDER BY id ASC LIMIT 2
id username
1 User1
2 User2

If the argument's value is 0, the result set will be empty.

Also notice that the ORDER BY clause may be important in order to specify the first rows of the result set that will be presented (when ordering by another column).

LIMITclause with two arguments[edit | edit source]

When two arguments are used in a LIMIT clause:

  • the first argument represents the row from which the result set rows will be presented – this number is often mentioned as an offset, since it represents the row previous to the initial row of the constrained result set. This allows the argument to receive 0 as value and thus taking into consideration the first row of the non-constrained result set.
  • the second argument specifies the maximum number of rows to be returned in the result set (similarly to the one argument's example).

Therefore the query:

SELECT * FROM users ORDER BY id ASC LIMIT 2, 3

Presents the following result set:

id username
3 User3
4 User4
5 User5

Notice that when the offset argument is 0, the result set will be equivalent to a one argument LIMIT clause. This means that the following 2 queries:

SELECT * FROM users ORDER BY id ASC LIMIT 0, 2

SELECT * FROM users ORDER BY id ASC LIMIT 2

Produce the same result set:

id username
1 User1
2 User2

OFFSET keyword: alternative syntax[edit | edit source]

An alternative syntax for the LIMIT clause with two arguments consists in the usage of the OFFSET keyword after the first argument in the following manner:

SELECT * FROM users ORDER BY id ASC LIMIT 2 OFFSET 3

This query would return the following result set:

id username
3 User3
4 User4

Notice that in this alternative syntax the arguments have their positions switched:

  • the first argument represents the number of rows to be returned in the result set;
  • the second argument represents the offset.

Credit:Stack_Overflow_Documentation