I tried to make a new table,c, renaming user1 to user2, user2 to user 1 and UNION ALL the original table.
WITH c AS (SELECT user1 AS user2, user2 AS user1, msg_count FROM f)
SELECT * FROM c UNION ALL (SELECT user1, user2, msg_count FROM f)
This is f, the original table:
|id| date |user1 |user2 |msg_count|
|--|--------------|---------|-----------|---------|
|1 | 2020-08-02|kpena |scottmartin|2|
|2 | 2020-08-02|misty19 |srogers |2|
|3 | 2020-08-02|jerome75 |craig23 |3|
**The question is: Why am I not getting a rename-columned table to UNION? My query returned a UNION of two identical original tables **
The output is this:
|id|user2 |user1 |msg_count|
|--|--------|-------------|---------|
|1|kpena | scottmartin| 2|
|2|misty19 | srogers | 2|
|3|jerome75| craig23 | 3|
|4|kpena | scottmartin| 2|
|5|misty19 | srogers | 2|
|6|jerome75| craig23 | 3|
which is not what I expected, I was expecting this instead:
|id|user2 |user1 |msg_count|
|--|-----------|-----------|---------|
|1|kpena |scottmartin| 2|
|2|misty19 |srogers | 2|
|3|jerome75 |craig23 | 3|
|4|scottmartin|kpena | 2|
|5|srogers |misty19 | 2|
|6|craig23 |jerome75 | 3|
Could someone please shed some light on this?
- Not sure if it is just me but the bottom two tables are not displaying correctly, and I couldn’t post my edit…
Thanks a lot!
>Solution :
To explain the issue, consider the output of just selecting from your CTE – you have renamed the columns, but the order (left to right) remains unchanged, so now think about select *…
Your desired results rely on you selecting the correct columns, not their order:
with c as (select user1 as user2, user2 as user1, msg_count from f)
select user1, user2, msg_count
from c
union all (select user1, user2, msg_count from f);