MariaDB 10.6

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

select version();


version()


10.6.4-MariaDB


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
2

4
6
68
1

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
2

4
6
68
1

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;
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE t
FROM test t
JOIN cte
     ON t.lpo_id = cte.lpo_id
     AND cte.rn > 1' at line 10

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

4
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	2
4	6	68	1
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	2
4	6	68	1
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
4	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	2
4	6	68	1
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	2
4	6	68	1
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
4	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	2
4	6	68	1
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	2
4	6	68	1
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
4	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