ColA | ColB | ColC |
---|---|---|
A | 1 | 12 |
A | 2 | 12 |
A | 3 | 12 |
B | 1 | 12 |
B | 2 | 12 |
B | 3 | 12 |
C | 1 | 12 |
C | 2 | 12 |
C | 3 | 12 |
I have such table data. And expect result is:
ColA | ColB | ColC |
---|---|---|
A | 1 | 12 |
B | 2 | 12 |
C | 3 | 12 |
or
ColA | ColB | ColC |
---|---|---|
A | 3 | 12 |
B | 2 | 12 |
C | 1 | 12 |
or
ColA | ColB | ColC |
---|---|---|
A | 1 | 12 |
B | 3 | 12 |
C | 2 | 12 |
If there are 4 record inserted, then it will join and become 16(4*4) records in final temp table. How do I delete unnecessary data in this case?
This is what tried
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS Row_count
FROM table_1
)
DELETE FROM cte WHERE rn<>1;
And I got:
ColA | ColB | ColC |
---|---|---|
A | 1 | 12 |
B | 1 | 12 |
C | 1 | 12 |
which is wrong.
ColB
data should be not be duplicated. After deleting, every record must be unique.
4条答案
按热度按时间vfwfrxfs1#
You can use combination of two partitions and check for differences:
See the db fiddle here https://dbfiddle.uk/aLsQLHIt
goqiplq22#
For this sample data all you need is
DENSE_RANK()
window function to get a group number for each value ofColA
:See the demo .
pkbketx93#
I'd to rank lines twice: first time on colB, colC and on colA so, the intermediate results would be:
| ColA | ColB | ColC | rank_bc | rank_a |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| A | 1 | 12 | 1 | 1 |
| A | 2 | 12 | 2 | 1 |
| A | 3 | 12 | 3 | 1 |
| B | 3 | 12 | 3 | 2 |
| B | 2 | 12 | 2 | 2 |
| B | 1 | 12 | 1 | 2 |
| C | 1 | 12 | 1 | 3 |
| C | 2 | 12 | 2 | 3 |
| C | 3 | 12 | 3 | 3 |
After that all is left to do is just to filter lines with rank_bc = rank_a
dbfiddle
uinbv5nw4#
You Can solve your problem with cte and window function with use query store
Result: