How to get "top X with the rest" in SQL?

Question

Let's say I have a table with the columns "name" and "quantity", and I want to get the first X-products, which are the most real, and another row, which is the sum of all the other products.

We can take this example SQL script as an example:

CREATE TEMPORARY TABLE product ( name TEXT, quantity INT ); INSERT INTO product (name, quantity) VALUES ('carrot', 5), ('tomato', 1), ('potato', 3), ('grape', 8), ('salad', 10); 

From this set of samples I want to get this result:

 name quantity ---------------- salad 10 grape 8 others 9 

I am currently using the following solution, but I am wondering if there is something more beautiful and / or more effective:

 WITH top AS ( SELECT name, quantity FROM product ORDER BY quantity DESC LIMIT 2), without AS ( SELECT 'others' AS other, sum(product.quantity) AS quantity FROM product WHERE product.name NOT IN (SELECT name FROM top) GROUP BY other) SELECT name, quantity FROM (SELECT name, quantity FROM top UNION SELECT other, quantity FROM without) AS t ORDER BY quantity DESC; 

Answer 1

Using:

 WITH summary AS ( SELECT p.*, ROW_NUMBER() OVER (ORDER BY p.quantity DESC) AS rank FROM PRODUCT p) SELECT s.name, s.quantity, s.rank FROM summary s WHERE s.rank <= 2 UNION ALL SELECT 'other', SUM(t.quantity), 3 FROM summary t WHERE t.rank > 2 ORDER BY rank, quantity DESC 

Regarding UNION

If you need to remove duplicates, use UNION . Otherwise, use the faster UNION ALL alternative to combine the queries with the corresponding data types in the position in the SELECT clause without deleting duplicates (and be faster for it).

Do not use a view / inline view unless you need to

It:

 SELECT name, quantity FROM (SELECT name, quantity FROM top UNION SELECT other, quantity FROM without) AS t ORDER BY quantity DESC; 

.. only should be:

 SELECT name, quantity FROM top UNION SELECT other, quantity FROM without ORDER BY quantity DESC