Question 229726 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Select Query 80% DESC and 20% ASC

I have Database table called tbl_emails like this

    id   email

    1    [email protected]
    2    [email protected]
    3    [email protected]
    4    [email protected]
    5    [email protected]
    6    [email protected]
    7    [email protected]
    8    [email protected]
    9    [email protected]
    10   [email protected]

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!

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    use the UNION ALL operator to combine the results of two separate queries that retrieve the required percentages of emails.

    SELECT email
FROM tbl_emails
ORDER BY id DESC
LIMIT (SELECT CEIL(COUNT(*) * 0.8) FROM tbl_emails)
UNION ALL
SELECT email
FROM tbl_emails
ORDER BY id ASC
LIMIT (SELECT CEIL(COUNT(*) * 0.2) FROM tbl_emails);

    let me know if this can help you

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search