MariaDB 10.4

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

select version();


version()


10.4.21-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,
  LEAST(lang_1, lang_2), 
  GREATEST(lang_1, lang_2)
FROM test;


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


1
4
6

2
4
6

3
6
68

4
6
68

5
6
18

6
6
18

7
18
36

8
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


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 FROM test t 
WHERE t.lpo_id IN 
(
  SELECT c.lpo_id FROM cte c
  WHERE c.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 FROM test t 
WHERE t.lpo_id IN 
(
  SELECT c.lpo_id FROM cte c
  WHERE...' at line 10

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

2
4
6

3
6
68

4
6
68

5
6
18

6
6
18

7
18
36

8
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


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 ORDER BY lpo_id
)
DELETE FROM test t 
WHERE t.lpo_id IN 
(
  SELECT lpo_id FROM cte c
  WHERE c.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 FROM test t 
WHERE t.lpo_id IN 
(
  SELECT lpo_id FROM cte c
  WHERE c...' at line 10

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
);
SELECT * FROM test ORDER BY test.lpo_id;


lpo_id
lang_1
lang_2


1
6
4

3
6
68

6
6
18

7
36
18

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	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)
1	4	6
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	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	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
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	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
1	6	4
3	6	68
6	6	18
7	36	18
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	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)
1	4	6
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	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	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
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	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
1	6	4
3	6	68
6	6	18
7	36	18
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	LEAST(lang_1, lang_2)	GREATEST(lang_1, lang_2)
1	4	6
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	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	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
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	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
1	6	4
3	6	68
6	6	18
7	36	18
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