LIMIT

LIMIT clause is used to limit the number of rows returned by a query. This clause is particularly useful when working with large data sets, as it allows for faster query performance by reducing the amount of data that needs to be processed.

Syntax

The basic syntax of the LIMIT clause is as follows:

  1. SELECT column1, column2, ...
  2. FROM table_name
  3. LIMIT number_of_rows;

The number_of_rows parameter specifies the maximum number of rows to be returned. If the value of this parameter is negative, no rows will be returned.

Examples

Consider the following table named “system_metrics”:

  1. +-------+-------+----------+-------------+-----------+---------------------+
  2. | host  | idc   | cpu_util | memory_util | disk_util | ts                  |
  3. +-------+-------+----------+-------------+-----------+---------------------+
  4. | host1 | idc_a |     11.8 |        10.3 |      10.3 | 2022-11-03 03:39:57 |
  5. | host1 | idc_b |       50 |        66.7 |      40.6 | 2022-11-03 03:39:57 |
  6. | host1 | idc_c |     50.1 |        66.8 |      40.8 | 2022-11-03 03:39:57 |
  7. | host1 | idc_e |     NULL |        66.7 |      40.6 | 2022-11-03 03:39:57 |
  8. | host2 | idc_a |     80.1 |        70.3 |        90 | 2022-11-03 03:39:57 |
  9. +-------+-------+----------+-------------+-----------+---------------------+

To retrieve the top 3 rows by memory_util, we can use theLIMIT clause:

  1. SELECT host, idc, memory_util
  2. FROM system_metrics
  3. ORDER BY memory_util DESC
  4. LIMIT 3;

The result of the above query would be:

  1. +-------+-------+-------------+
  2. | host  | idc   | memory_util |
  3. +-------+-------+-------------+
  4. | host2 | idc_a |        70.3 |
  5. | host1 | idc_c |        66.8 |
  6. | host1 | idc_b |        66.7 |
  7. +-------+-------+-------------+

LIMIT n, m allows to select the m rows from the result after skipping the first n rows. The LIMIT m OFFSET n syntax is equivalent.

  1. SELECT host, idc, memory_util
  2. FROM system_metrics
  3. ORDER BY memory_util DESC
  4. LIMIT 2 OFFSET 1;

OR

  1. SELECT host, idc, memory_util
  2. FROM system_metrics
  3. ORDER BY memory_util DESC
  4. LIMIT 1, 2;

The result of the above query would be:

  1. +-------+-------+-------------+
  2. | host  | idc   | memory_util |
  3. +-------+-------+-------------+
  4. | host1 | idc_c |        66.8 |
  5. | host1 | idc_b |        66.7 |
  6. +-------+-------+-------------+