sqlsql-servergroup-byselect-query

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

Solution

  • 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