Let’s say that I have database table:
| id | value | rank | | -----------|-----------|-------------| | 303 | D | 3 | | 404 | A | 1 | | 505 | B | 1 | | 505 | D | 4 | | 202 | B | 1 | | 505 | A | 5 | | 303 | N | 2 | | 101 | A | 1 | | 505 | A | 7 | | 202 | A | 6 | | 202 | N | 3 | | 505 | N | 3 | | 202 | A | 4 | | 505 | A | 2 | | 202 | N | 5 | | 303 | A | 1 | | 505 | N | 6 | | 202 | A | 2 |
Following:
SELECT * FROM table_name GROUP BY id ORDER BY rank;
I get:
| id | value | rank | | -----------|-----------|-------------| | 101 | A | 1 | | 202 | B | 1 | | 202 | A | 2 | | 202 | N | 3 | | 202 | A | 4 | | 202 | N | 5 | | 202 | A | 6 | | 303 | A | 1 | | 303 | N | 2 | | 303 | D | 3 | | 404 | A | 1 | | 505 | B | 1 | | 505 | A | 2 | | 505 | N | 3 | | 505 | D | 4 | | 505 | A | 5 | | 505 | N | 6 | | 505 | A | 7 |
However, for each group, I’d like to append an additional row with the value column taken from the id column so that the resulting table looks like:
| id | value | rank | | -----------|-----------|-------------| | 101 | A | 1 | | 101 | 101 | 2 | | 202 | B | 1 | | 202 | A | 2 | | 202 | N | 3 | | 202 | A | 4 | | 202 | N | 5 | | 202 | A | 6 | | 202 | 202 | 7 | | 303 | A | 1 | | 303 | N | 2 | | 303 | D | 3 | | 303 | 303 | 4 | | 404 | A | 1 | | 404 | 404 | 2 | | 505 | B | 1 | | 505 | A | 2 | | 505 | N | 3 | | 505 | D | 4 | | 505 | A | 5 | | 505 | N | 6 | | 505 | A | 7 | | 505 | 505 | 8 |
What is ANSI SQL (or most database agnostic) way to accomplish this?
Advertisement
Answer
You don’t want a group by in the initial set as you appear to want all the rows back:
select "id", "value", "rank"
from T
union all
select "id", cast("id" as varchar(10)), max("rank") + 1
from T
group by "id"
order by "id", "rank";
And you can do this with grouping sets for the fun of it:
select "id",
grouping("rank"),
case when grouping("rank") = 0 then min("value") else cast("id" as varchar(10)) end as "value",
case when grouping("rank") = 0 then "rank" else max("rank") over (partition by "id") + 1 end as "rank"
from T
group by grouping sets ("id", "rank"), ("id")
order by "id", "rank";