MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.26


CREATE TABLE test
(
  lpo_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  lang_1 INTEGER NOT NULL,
  lang_2 INTEGER NOT NULL,
  
  CONSTRAINT l1_ne_l2_ck CHECK (lang_1 != lang_2)
);
INSERT INTO test (lang_1, lang_2) VALUES
(6, 4),
(4, 6),
(6, 68),
(68, 6),
(18, 6),
(6, 18),
(36, 18),
(18, 36),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),

(2, 3),
(2, 4),
(2, 5),
(2, 6),
(2, 7),
(2, 8);
SELECT 
  lpo_id, lang_1, lang_2,
  LEAST(lang_1, lang_2), 
  GREATEST(lang_1, lang_2)
FROM test;


lpo_id
lang_1
lang_2
LEAST(lang_1, lang_2)
GREATEST(lang_1, lang_2)


1
6
4
4
6

2
4
6
4
6

3
6
68
6
68

4
68
6
6
68

5
18
6
6
18

6
6
18
6
18

7
36
18
18
36

8
18
36
18
36

9
1
2
1
2

10
1
3
1
3

11
1
4
1
4

12
1
5
1
5

13
1
6
1
6

14
1
7
1
7

15
1
8
1
8

16
2
3
2
3

17
2
4
2
4

18
2
5
2
5

19
2
6
2
6

20
2
7
2
7

21
2
8
2
8


SELECT 
  lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
FROM test
ORDER BY lpo_id;


lpo_id
LEAST(lang_1, lang_2)
GREATEST(lang_1, lang_2)
rn


1
4
6
1

2
4
6
2

3
6
68
1

4
6
68
2

5
6
18
1

6
6
18
2

7
18
36
1

8
18
36
2

9
1
2
1

10
1
3
1

11
1
4
1

12
1
5
1

13
1
6
1

14
1
7
1

15
1
8
1

16
2
3
1

17
2
4
1

18
2
5
1

19
2
6
1

20
2
7
1

21
2
8
1


WITH cte AS
(
  SELECT lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
  FROM test
)
SELECT * FROM cte
ORDER BY lpo_id;


lpo_id
LEAST(lang_1, lang_2)
GREATEST(lang_1, lang_2)
rn


1
4
6
1

2
4
6
2

3
6
68
1

4
6
68
2

5
6
18
1

6
6
18
2

7
18
36
1

8
18
36
2

9
1
2
1

10
1
3
1

11
1
4
1

12
1
5
1

13
1
6
1

14
1
7
1

15
1
8
1

16
2
3
1

17
2
4
1

18
2
5
1

19
2
6
1

20
2
7
1

21
2
8
1


WITH cte AS
(
  SELECT t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
  FROM test t
)
DELETE t
FROM test t
JOIN cte
     ON t.lpo_id = cte.lpo_id
     AND cte.rn > 1;
DELETE t
FROM test t
JOIN 
(
  SELECT t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
  FROM test t
)
  AS cte
     ON t.lpo_id = cte.lpo_id
     AND cte.rn > 1;
SELECT 
  t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2)
FROM test t ORDER BY t.lpo_id


lpo_id
LEAST(t.lang_1, t.lang_2)
GREATEST(t.lang_1, t.lang_2)


1
4
6

3
6
68

5
6
18

7
18
36

9
1
2

10
1
3

11
1
4

12
1
5

13
1
6

14
1
7

15
1
8

16
2
3

17
2
4

18
2
5

19
2
6

20
2
7

21
2
8


EXPlAIN ANALYZE
DELETE t
FROM test t
JOIN 
(
  SELECT t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
  FROM test t
) AS tab
ON t.lpo_id = tab.lpo_id
AND tab.rn > 1;


EXPLAIN


-> Delete from t (buffered)
    -> Nested loop inner join  (cost=6.40 rows=6) (actual time=0.117..0.117 rows=0 loops=1)
        -> Filter: (tab.rn > 1)  (cost=0.78..4.41 rows=6) (actual time=0.116..0.116 rows=0 loops=1)
            -> Table scan on tab  (cost=2.50..2.50 rows=0) (actual time=0.001..0.002 rows=17 loops=1)
                -> Materialize  (cost=2.50..2.50 rows=0) (actual time=0.109..0.113 rows=17 loops=1)
                    -> Window aggregate: row_number() OVER (PARTITION BY least(t.lang_1,t.lang_2),greatest(t.lang_1,t.lang_2) )   (actual time=0.080..0.092 rows=17 loops=1)
                        -> Sort: least(t.lang_1,t.lang_2), greatest(t.lang_1,t.lang_2)  (cost=1.95 rows=17) (actual time=0.075..0.078 rows=17 loops=1)
                            -> Table scan on t  (cost=1.95 rows=17) (actual time=0.021..0.052 rows=17 loops=1)
        -> Single-row index lookup on t using PRIMARY (lpo_id=tab.lpo_id)  (cost=0.27 rows=1) (never executed)



EXPLAIN ANALYZE
DELETE FROM test
WHERE test.lpo_id IN
(
  SELECT tab.lpo_id FROM
  (
    SELECT 
      t.lpo_id, LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2), 
      ROW_NUMBER() OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
    FROM test t ORDER BY t.lpo_id
  ) AS tab
  WHERE tab.rn > 1
);


EXPLAIN


-> Delete from test (buffered)
    -> Nested loop inner join  (cost=9.52 rows=6) (actual time=0.087..0.087 rows=0 loops=1)
        -> Table scan on <subquery2>  (cost=0.45..2.56 rows=6) (actual time=0.000..0.000 rows=0 loops=1)
            -> Materialize with deduplication  (cost=5.43..7.54 rows=6) (actual time=0.086..0.086 rows=0 loops=1)
                -> Filter: (tab.rn > 1)  (cost=0.78..4.41 rows=6) (actual time=0.084..0.084 rows=0 loops=1)
                    -> Table scan on tab  (cost=2.50..2.50 rows=0) (actual time=0.000..0.001 rows=17 loops=1)
                        -> Materialize  (cost=2.50..2.50 rows=0) (actual time=0.081..0.083 rows=17 loops=1)
                            -> Sort: t.lpo_id  (actual time=0.069..0.070 rows=17 loops=1)
                                -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.002 rows=17 loops=1)
                                    -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.059..0.061 rows=17 loops=1)
                                        -> Window aggregate: row_number() OVER (PARTITION BY least(t.lang_1,t.lang_2),greatest(t.lang_1,t.lang_2) )   (actual time=0.039..0.046 rows=17 loops=1)
                                            -> Sort: least(t.lang_1,t.lang_2), greatest(t.lang_1,t.lang_2)  (cost=1.95 rows=17) (actual time=0.037..0.038 rows=17 loops=1)
                                                -> Table scan on t  (cost=1.95 rows=17) (actual time=0.011..0.027 rows=17 loops=1)
        -> Single-row index lookup on test using PRIMARY (lpo_id=`<subquery2>`.lpo_id)  (cost=1.52 rows=1) (never executed)



EXPLAIN ANALYZE
WITH cte AS
(
  SELECT lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
  FROM test
)
DELETE FROM test t 
WHERE t.lpo_id IN 
(
  SELECT lpo_id FROM cte
  WHERE rn > 1
);


EXPLAIN


-> Delete from t (buffered)
    -> Nested loop inner join  (cost=9.52 rows=6) (actual time=0.058..0.058 rows=0 loops=1)
        -> Table scan on <subquery2>  (cost=0.45..2.56 rows=6) (actual time=0.000..0.000 rows=0 loops=1)
            -> Materialize with deduplication  (cost=5.43..7.54 rows=6) (actual time=0.058..0.058 rows=0 loops=1)
                -> Filter: (cte.rn > 1)  (cost=0.78..4.41 rows=6) (actual time=0.056..0.056 rows=0 loops=1)
                    -> Table scan on cte  (cost=2.50..2.50 rows=0) (actual time=0.000..0.001 rows=17 loops=1)
                        -> Materialize CTE cte  (cost=2.50..2.50 rows=0) (actual time=0.052..0.054 rows=17 loops=1)
                            -> Window aggregate: row_number() OVER (PARTITION BY least(test.lang_1,test.lang_2),greatest(test.lang_1,test.lang_2) )   (actual time=0.039..0.046 rows=17 loops=1)
                                -> Sort: least(test.lang_1,test.lang_2), greatest(test.lang_1,test.lang_2)  (cost=1.95 rows=17) (actual time=0.037..0.038 rows=17 loops=1)
                                    -> Table scan on test  (cost=1.95 rows=17) (actual time=0.010..0.026 rows=17 loops=1)
        -> Single-row index lookup on t using PRIMARY (lpo_id=`<subquery2>`.lpo_id)  (cost=1.52 rows=1) (never executed)

lpo_id	lang_1	lang_2	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)
1	6	4	4	6
2	4	6	4	6
3	6	68	6	68
4	68	6	6	68
5	18	6	6	18
6	6	18	6	18
7	36	18	18	36
8	18	36	18	36
9	1	2	1	2
10	1	3	1	3
11	1	4	1	4
12	1	5	1	5
13	1	6	1	6
14	1	7	1	7
15	1	8	1	8
16	2	3	2	3
17	2	4	2	4
18	2	5	2	5
19	2	6	2	6
20	2	7	2	7
21	2	8	2	8

lpo_id	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)	rn
1	4	6	1
2	4	6	2
3	6	68	1
4	6	68	2
5	6	18	1
6	6	18	2
7	18	36	1
8	18	36	2
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)	rn
1	4	6	1
2	4	6	2
3	6	68	1
4	6	68	2
5	6	18	1
6	6	18	2
7	18	36	1
8	18	36	2
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	LEAST(t.lang_1, t.lang_2)	GREATEST(t.lang_1, t.lang_2)
1	4	6
3	6	68
5	6	18
7	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	lang_1	lang_2	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)
1	6	4	4	6
2	4	6	4	6
3	6	68	6	68
4	68	6	6	68
5	18	6	6	18
6	6	18	6	18
7	36	18	18	36
8	18	36	18	36
9	1	2	1	2
10	1	3	1	3
11	1	4	1	4
12	1	5	1	5
13	1	6	1	6
14	1	7	1	7
15	1	8	1	8
16	2	3	2	3
17	2	4	2	4
18	2	5	2	5
19	2	6	2	6
20	2	7	2	7
21	2	8	2	8

lpo_id	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)	rn
1	4	6	1
2	4	6	2
3	6	68	1
4	6	68	2
5	6	18	1
6	6	18	2
7	18	36	1
8	18	36	2
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)	rn
1	4	6	1
2	4	6	2
3	6	68	1
4	6	68	2
5	6	18	1
6	6	18	2
7	18	36	1
8	18	36	2
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	LEAST(t.lang_1, t.lang_2)	GREATEST(t.lang_1, t.lang_2)
1	4	6
3	6	68
5	6	18
7	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	lang_1	lang_2	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)
1	6	4	4	6
2	4	6	4	6
3	6	68	6	68
4	68	6	6	68
5	18	6	6	18
6	6	18	6	18
7	36	18	18	36
8	18	36	18	36
9	1	2	1	2
10	1	3	1	3
11	1	4	1	4
12	1	5	1	5
13	1	6	1	6
14	1	7	1	7
15	1	8	1	8
16	2	3	2	3
17	2	4	2	4
18	2	5	2	5
19	2	6	2	6
20	2	7	2	7
21	2	8	2	8

lpo_id	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)	rn
1	4	6	1
2	4	6	2
3	6	68	1
4	6	68	2
5	6	18	1
6	6	18	2
7	18	36	1
8	18	36	2
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)	rn
1	4	6	1
2	4	6	2
3	6	68	1
4	6	68	2
5	6	18	1
6	6	18	2
7	18	36	1
8	18	36	2
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	LEAST(t.lang_1, t.lang_2)	GREATEST(t.lang_1, t.lang_2)
1	4	6
3	6	68
5	6	18
7	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8