Pagination - Text comparison with greater than and less than with DESC

4 votes

1 answer

493 views

                          I am implementing a seek method  for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if you are sorting ASC or DESC. This works great for integers and dates but I am wondering how best to do it with text columns, specifically for the first page.

For example, for the first page when sorting by name it would be

    SELECT *
    FROM users
    WHERE first_name > ''
    ORDER BY first_name ASC
    LIMIT 5;

Then the next page would be

    SELECT *
    FROM users
    WHERE first_name > 'Caal'
    ORDER BY first_name ASC
    LIMIT 5;

This works great. I am unsure about DESC order though. This seems to work but I am unsure if it is 'correct'.

     SELECT 	*
     FROM 	users
     WHERE 	last_name < 'ZZZ'
     ORDER BY last_name DESC
     LIMIT 5;

Second page

    SELECT 	*
    FROM 	users
    WHERE 	last_name < 'Smith'
    ORDER BY last_name DESC
    LIMIT 5;

P.S. I am using the jooq  support for the seek method and prefer to not have to hack around the native support, so ideally there is a proper parameter to put in the 'ZZZ' place above. i.e. there WHERE part of the clause is mandatory.

                        

Asked by Collin Peters (775 rep)

Aug 12, 2015, 07:28 PM
Last activity: May 6, 2025, 07:05 AM

Pagination - Text comparison with greater than and less than with DESC

Related Questions