This section provides recommendations for organizing paginated data output.
To organize paginated output, we recommend selecting data sorted by primary key sequentially, limiting the number of rows with the LIMIT keyword.
The query in listing 1 demonstrates the recommended way to organize paginated output.
$lastCity, $lastNumber: Primary key values obtained from the previous query.
Listing 1: Query for organizing paginated output
-- Table `schools`: -- ┌─────────┬─────────┬─────┐ -- | Name | Type | Key | -- ├─────────┼─────────┼─────┤ -- | city | Utf8? | K0 | -- | number | Uint32? | K1 | -- | address | Utf8? | | -- └─────────┴─────────┴─────┘ DECLARE $limit AS Uint64; DECLARE $lastCity AS Utf8; DECLARE $lastNumber AS Uint32; $part1 = ( SELECT * FROM schools WHERE city = $lastCity AND number > $lastNumber ORDER BY city, number LIMIT $limit ); $part2 = ( SELECT * FROM schools WHERE city > $lastCity ORDER BY city, number LIMIT $limit ); $union = ( SELECT * FROM $part1 UNION ALL SELECT * FROM $part2 ); SELECT * FROM $union ORDER BY city, number LIMIT $limit;
NULL value in key column
In YDB, all columns, including key ones, may have a NULL value. Despite this, using NULL as key column values is highly discouraged, since the SQL standard doesn't allow NULL to be compared. As a result, concise SQL statements with simple comparison operators won't work correctly. Instead, you'll have to use cumbersome statements with IS NULL/IS NOT NULL expressions.