S-SQL Examples F

Fetch is a more efficient way to do pagination instead of using limit and offset. Fetch allows you to retrieve a limited set of rows, optionally offset by a specified number of rows. In order to ensure this works correctly, you should use the order-by clause. If the amount is not provided, it assumes you only want to return 1 row. https://www.postgresql.org/docs/current/sql-select.html

Examples:

(query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5))

((1) (2) (3) (4) (5))

(query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5 10))

((11) (12) (13) (14) (15))

(query (:select (:as (:count '* :distinct) 'unfiltered)
                (:as (:count '* :filter (:= 1 'bid))
                     'filtered)
                :from 'testtable))

Note that, if used, the filter must be last in the count args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause. E.g.

(query (:select (:count '*)
                (:count '* :filter (:= 1 'bid))
                'id
                :from 'pbbench-history))