Black Friday sale is now on! 50% off Laravel: The Modular Way. Learn more

MySQL next record order by title

David Carr


    A common requirement for web applications is the ability to be able to move to the next and previous record when viewing a record, think of a leads system where a sales person would go through the leads one by one by clicking a next or previous button whilst viewing the record. 

    Making a previous and next query for MySQL is simple enough but if you want to go through the order alphabetically instead of id order it’s a little more tricky. 

    Using a UNION query means you can get both the previous and next record in one query. How this works is first select the columns from the table where the column is less that the current record, then order that by that column in descending order to get the last record.

    For the next query it’s the same thing but don’t use descending in the order.

    (SELECT id, title FROM companies WHERE title < 'Fictional Company' ORDER BY title desc LIMIT 1)
    (SELECT id, title FROM companies WHERE title > 'Fictional Company' ORDER BY title LIMIT 1)

    So this query will return 2 records as long as there is a next and previous record.

    Laravel Modules Book by David Carr

    Help support the blog so that I can continue creating new content!

    Subscribe to my newsletter

    Subscribe and get my books and product announcements.

    © 2009 - 2022 DC Blog. All code MIT license. All rights reserved.