Search code examples
mysqllimit

selecting the largest n values and smallest n values simulataneously from a table mySQL

In mySQL, I have a database of country names and population I want to return the 5 most populated and 5 least populated countries. I understanding how to order the results by population and how to limit the results to the top 'n' values or bottom 'n' values of a column, but cannot determine how to return both the top and bottom at the by using one query.

HERE are the two codes separately...

SELECT Name, population FROM country 
ORDER BY GNP DESC LIMIT 5; 

SELECT Name, GNP FROM country 
ORDER GNP LIMIT 5;

How can I combine these codes into one output??

THanks

Solution

  • You need UNION ALL:

    (
  SELECT Name, population FROM country 
  ORDER BY population DESC LIMIT 5
)
UNION ALL
(
  SELECT Name, population FROM country 
  ORDER BY population ASC LIMIT 5
)
ORDER by population DESC;

    See a simplified demo.