CODEWITHSUNDEEP
mysqlsql
Vladimir Hraban
Vladimir Hraban

Reputation: 3581

Mysql - get highest and lowest 5 results

I have a query

SELECT dmn_name AS domain, COUNT(*) AS newsletters, SUM(mwl_sended) AS emailsSent, SUM(IFNULL(nwl_ca, 0)/mwl_sended) * 1000 AS ecpm FROM domain
                JOIN newsletter USING(dmn_id)
                WHERE dmn_actif = 1
                AND nwl_sendedOn > DATE_SUB(NOW(), INTERVAL 1 MONTH)
                AND nwl_sendingEndedOn is NOT NULL
                GROUP BY dmn_name
                ORDER BY ecpm DESC
                LIMIT 5

It returns 5 results having highest ecpm. How would I modify it to return 5 highest AND 5 lowest? I want to avoid executing the query two times.

Thanks

Upvotes: 0

Views: 222

Answers (1)

John Conde
John Conde

Reputation: 219834

Use UNION ALL

SELECT dmn_name AS domain, COUNT(*) AS newsletters, SUM(mwl_sended) AS emailsSent, SUM(IFNULL(nwl_ca, 0)/mwl_sended) * 1000 AS ecpm FROM domain
                JOIN newsletter USING(dmn_id)
                WHERE dmn_actif = 1
                AND nwl_sendedOn > DATE_SUB(NOW(), INTERVAL 1 MONTH)
                AND nwl_sendingEndedOn is NOT NULL
                GROUP BY dmn_name
                ORDER BY ecpm DESC
                LIMIT 5
UNION ALL
SELECT dmn_name AS domain, COUNT(*) AS newsletters, SUM(mwl_sended) AS emailsSent, SUM(IFNULL(nwl_ca, 0)/mwl_sended) * 1000 AS ecpm FROM domain
                JOIN newsletter USING(dmn_id)
                WHERE dmn_actif = 1
                AND nwl_sendedOn > DATE_SUB(NOW(), INTERVAL 1 MONTH)
                AND nwl_sendingEndedOn is NOT NULL
                GROUP BY dmn_name
                ORDER BY ecpm ASC
                LIMIT 5
ORDER BY ecpm DESC

Upvotes: 1

Related Questions