Add all rows after the first two rows in sql-CodePudding

car sum_of_solds rank

honda 150000 2

mazda 120000 3

tesla 180000 1

toyota 60000 4

I want to add all rows after the first two rows rank wise. So for in the above example, I want something like this:

car sum_of_solds rank

tesla 150000 1

honda 120000 2

others 180000 others

CodePudding user response：

You may use a union query:

SELECT car, sum_of_solds, "rank"
FROM
(
    SELECT car, sum_of_solds, CAST("rank" AS varchar(10)) AS "rank", "rank" AS pos
    FROM yourTable
    WHERE "rank" <= 2
    UNION ALL
    SELECT 'others', SUM(sum_of_solds), 'others', 3
    FROM yourTable
    WHERE "rank" > 2
) t
ORDER BY pos;