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
CASE WHEN name1 < name2 THEN name1 ELSE name2 END,
CASE WHEN name1 < name2 THEN name2 ELSE name1 END,
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 |