LIMIT 子句

適用於：核取記號為「是」Databricks SQL 核取記號為「是」Databricks Runtime

限制查詢所傳回的數據列數目。 一般而言，這個子句會與 ORDER BY 搭配使用，以確保結果具決定性。

語法

LIMIT { ALL | integer_expression }

參數

  • 全部

    如果指定，查詢會傳回所有數據列。 換句話說，如果指定此選項，則不會套用任何限制。

  • integer_expression

    傳回整數的字面值表達式。 若表達式不可摺疊、非整數型態、值值為 NULL，或值為負值，Azure Databricks 會升起 INVALID_LIMIT_LIKE_EXPRESSION。

常見錯誤條件

範例

> CREATE TEMP VIEW person (name, age)
    AS VALUES ('Zen Hui', 25),
              ('Anil B' , 18),
              ('Shone S', 16),
              ('Mike A' , 25),
              ('John A' , 18),
              ('Jack N' , 16);

-- Select the first two rows.
> SELECT name, age FROM person ORDER BY name LIMIT 2;
 Anil B  18
 Jack N  16

-- Select the 4th and 5th rows by alphabetical order.
> SELECT name, age FROM person ORDER BY name LIMIT 2 OFFSET 3;
  Mike A  25
 Shone S  16

-- Specifying ALL option on LIMIT returns all the rows.
> SELECT name, age FROM person ORDER BY name LIMIT ALL;
  Anil B  18
  Jack N  16
  John A  18
  Mike A  25
 Shone S  16
 Zen Hui  25

-- A function expression as an input to LIMIT.
> SELECT name, age FROM person ORDER BY name LIMIT length('SPARK');
  Anil B  18
  Jack N  16
  John A  18
  Mike A  25
 Shone S  16

-- A non-literal expression as an input to LIMIT is not allowed.
> SELECT name, age FROM person ORDER BY name LIMIT length(name);
  Error: INVALID_LIMIT_LIKE_EXPRESSION

-- A negative LIMIT is not allowed.
> SELECT name, age FROM person LIMIT -1;
  Error: INVALID_LIMIT_LIKE_EXPRESSION
  • Last updated on