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

Question

Say I have a table with a 'name' and 'quantity' columns, and I want to get the first X products which are the most present, and another line, which is the sum of all the other products.

We can take this sample 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 sample set, I want to retrieve this kind of result:

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

I'm currently using the following solution, but I wonder if there's something more pretty and/or more efficient:

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

Use:

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 alternative, UNION ALL, to combine queries with matching data types at the position in the SELECT clause without removing duplicates (and being faster for it).

Don't use a Derived Table/Inline View if You Don't Have to

This:

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

..only needs to be:

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