Paginated output

• Examples of paginated output implementation

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.

Note

$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.

Examples of paginated output implementation

• C++
• Java
• Python
• Go