SQL query - comparing two items on distinct item - REVISITED

As an addon to the question I asked a few days ago here at sql-query-comparing-two-items-on-distinct-item would it be possible to remove the possibillty of null values where one of the 'pers' does not occur in the 'evt'.

Output

**EVT   John    Paul    Difference**
A1      1       2       -1
A2      2       3       -1
A3      2       NULL    [errors here] - ** item to be removed **

Data

**EVT   PERS    RANK**
A1      John    1
A1      Paul    2
A1      Ringo   3
A1      George  4
A2      Ringo   1
A2      John    2
A2      Paul    3
A2      George  4
A3      Ringo   1
A3      John    2
A3      George  4

Current Code (thanks to Martin Smith)

SELECT 
      EVT,
      MAX(CASE WHEN Pers='John' THEN Rank END) AS John,
      MAX(CASE WHEN Pers='Paul' THEN Ra开发者_StackOverflow社区nk END) AS Paul,
      MAX(CASE WHEN Pers='John' THEN Rank END) - MAX(CASE WHEN Pers='Paul' THEN Rank END) as Difference
FROM YourTable 
WHERE Pers IN ('John','Paul')
GROUP BY EVT

Thanks in advance

You can add a having clause:

GROUP BY EVT
HAVING MAX(CASE WHEN Pers='John' THEN Rank END) IS NOT NULL
       AND MAX(CASE WHEN Pers='Paul' THEN Rank END) IS NOT NULL

Or

WITH NullSNotYetExcluded AS (

  --insert your SELECT statement from Martin here--

)
  SELECT * FROM NullsNotYetExcluded 
  WHERE Difference IS NOT NULL;

Another option (probably more costly to run) is to add

HAVING COUNT(DISTINCT Pers) = 2