By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE fruits (
name1 VARCHAR(50),
name2 VARCHAR(50),
year1 INT,
year2 INT
);
INSERT INTO fruits (name1, name2, year1, year2)
VALUES
('apple', 'pear', 2010, 2001),
('apple', 'pear', 2011, 2002),
('pear', 'apple', 2010, 2003),
('pear', 'apple', 2011, 2004),
('apple', NULL, 2009, 2005),
('pear', 'orange', 2008, 2006),
('apple', 'pear', 2010, 2007),
('apple', 'grape', 2010, 2008);
8 rows affected
WITH ranked_names AS (
SELECT
name1,
name2,
year1,
year2,
ROW_NUMBER() OVER (PARTITION BY LEAST(name1, name2), GREATEST(name1, name2), year1 ORDER BY year2) AS rn
FROM
fruits
)
SELECT
name1,
name2,
year1,
year2
FROM
ranked_names
WHERE
rn = 1;
| name1 | name2 | year1 | year2 |
|---|---|---|---|
| apple | null | 2009 | 2005 |
| apple | grape | 2010 | 2008 |
| apple | pear | 2010 | 2001 |
| apple | pear | 2011 | 2002 |
| pear | orange | 2008 | 2006 |