Let’s say that I have database table:
x
| 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_nameGROUP BY idORDER 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 Tunion allselect "id", cast("id" as varchar(10)), max("rank") + 1from Tgroup 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 Tgroup by grouping sets ("id", "rank"), ("id")order by "id", "rank";