How do we get the highest 2 values and the lowest 2 values of the same column in sql-CodePudding

I have an sql table with products and price and I want to get 2 products with the highest price and 2 with the lowest price.

SELECT title, price
FROM products
GROUP BY price
ORDER BY price ASC limit 2

I tried this to get the lowest 2 values but how do I get the highest 2 in the same query?

CodePudding user response：

Use a union of two limit queries:

(SELECT title, price FROM products ORDER BY price LIMIT 2)
UNION ALL
(SELECT title, price FROM products ORDER BY price DESC LIMIT 2)

The subqueries on the top and bottom will return the records corresponding to the 2 lowest and 2 highest prices, respectively.