MySQL Select Query 80% DESC and 20% ASC

August 14, 2023

I have Database table called tbl_emails like this

    id   email

    1    example1@example.com
    2    example2@example.com
    3    example3@example.com
    4    example4@example.com
    5    example5@example.com
    6    example6@example.com
    7    example7@example.com
    8    example8@example.com
    9    example9@example.com
    10   example10@example.com

I have about 5000 rows in the table. Now What I am looking to do is get 100 emails from it. But I want 80 rows means 80% DESC and 20% ASC by id, currently I am doing it by multiple queries and merging result in PHP. I am wondering its possible in single SQL query?

Thanks!

>Solution :

You can use UNION ALLto join the results of 2 queries with the same columns.

SELECT * FROM (
    SELECT id, email
    FROM tbl_emails
    ORDER BY ID ASC
    LIMIT 80
) AS top
UNION ALL
SELECT * FROM (
    SELECT id, email 
    FROM tbl_emails
    ORDER BY ID DESC
    LIMIT 20
) AS bottom;

If you need to preserve order of the rows in the ouput, you can in addition use variables to store the row count in each query

set @top := 0;
set @bottom := 0; 
SELECT * FROM (
    SELECT id, email, @top := @top+1 AS top, @bottom AS bottom
    FROM tbl_emails
    ORDER BY ID ASC
    LIMIT 80
) AS top
UNION ALL
SELECT * FROM (
    SELECT id, email, @top AS top, @bottom := @bottom + 1 AS bottom  
    FROM tbl_emails
    ORDER BY ID DESC
    LIMIT 20
) AS bottom
ORDER BY top, bottom;