user1415791 user1415791 - 4 years ago 113
SQL Question

SQL grouping related columns

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download