Suppose I have a table with 2 columns like this:
Person1, Person2
David Jessica
Jessica David
David Oz
Oz David
Guy Richard
Richard Guy
Jessica Oz
Oz Jessica
and another table with 2 columns:
Person Last Posted
David 12/8/2016
Jessica 5/10/2016
Oz 23/11/2016
Guy 8/3/2016
Richard 27/6/2016
assuming a players name is unique and will appear only once. how to choose the person with the latest post? A related group for this example is (David ,Jessica, Oz),(Guy, Richard)
The output should be:
Person
Oz
Richard
This one should work. In the first sub-query, called winner, list the person with the most recent post for each pairing. Then join this to a second sub-query, called loser, which lists the person with the least recent post per pairing. The output should return people who are in the winners sub-query but not the losers one. Being in the losers sub-query would demonstrate that someone in your group had a more recent post than you:
SELECT DISTINCT winner.person
FROM
(SELECT
CASE WHEN t2_1.last_post > t2_2.last_post THEN person1 ELSE person2 END AS person
FROM t1
INNER JOIN t2 t2_1 ON t1.person1 = t2_1.person
INNER JOIN t2 t2_2 ON t1.person2 = t2_2.person) winner
LEFT JOIN
(SELECT
CASE WHEN t2_1.last_post < t2_2.last_post THEN person1 ELSE person2 END AS person
FROM t1
INNER JOIN t2 t2_1 ON t1.person1 = t2_1.person
INNER JOIN t2 t2_2 ON t1.person2 = t2_2.person) loser
ON winner.person = loser.person
WHERE loser.person IS NULL
Tested here: http://sqlfiddle.com/#!9/6ef390/13