CODEWITHSUNDEEP
mysqlsql
fmalina
fmalina

Reputation: 6310

How to find which page something starts on in a numbered pagination of sorted results?

/users/1 is a 1st page of paginated results. It runs a query:

SELECT * FROM users ORDER BY surname, id LIMIT 0, 100;

/users/2 adjusts the generated SQL. LIMIT 100, 100 shows 2nd page of paginated results etc.

I want to implement A-Z links to these same paginated results, when they are ordered by surname; year based links when ordered by date; number based links when ordered by an integer field. You get the point.

e.g.: URL /users/A will redirect to /users/1, but how do I figure out efficiently which page should /users/B redirect to.

I know /users/B could just run a "filter" query to pull out users with surname starting B a display that on the page. However I want to avoid creating another page, hence I want a redirect.

I could also iterate over the results of SELECT surname FROM users ORDER BY surname counting where the B and other letters start and divide starting position by results per page (100), but that's inefficient. I think this needs some clever SQL. I use MySQL.

Similar question that uses Postgres: Pagination: Find out which page an item is on (given primary key & sorting order)

Upvotes: 1

Views: 199

Answers (2)

muhmud
muhmud

Reputation: 4604

How about something like this:

select Page
from (
    select substring(Surname, 1, 1) as SurnameFirstLetter, min(page) as Page
    from (
        select Surname, ceiling((@row := @row + 1) / 100) as page
        from users, (select @row := 0) r
        order by Surname
    ) t
    group by substring(Surname, 1, 1)
) t
where SurnameFirstLetter = 'B'

Upvotes: 3

Jack
Jack

Reputation: 1912

You can use select count(*) from users where surname<'Current' to get current position.

Upvotes: 2

Related Questions