title: Remove rows from table based on column value using self join
body: <p>I have a requirement where my data looks like below. I have to find the ids from the table where the last pid status is not "removed".</p>
  
  <p>Note:-
  1. To get the last pid status use the "date" and "hour" columns.
  2. If for an "id", a pid's last "status" value is removed then  don't include that row in the result.</p>
  
  <pre><code>id  |   key    |    date     |  hour    | pid  | status
  --------------------------------------------------------
  id1 |   one    |    20180618 |  2       |  p1  | added
  id1 |   one    |    20180618 |  3       |  p1  | removed
  id1 |   one    |    20180618 |  4       |  p1  | added
  id1 |   one    |    20180618 |  4       |  p2  | added
  
  id1 |   one    |    20180619 |  2       |  p1  | removed
  id1 |   one    |    20180619 |  4       |  p1  | added
  id1 |   one    |    20180619 |  4       |  p2  | removed
  id1 |   one    |    20180619 |  5       |  p3  | added
  
  id2 |   one    |    20180619 |  5       |  p1  | added
  id2 |   one    |    20180619 |  5       |  p2  | added
  id2 |   one    |    20180619 |  6       |  p1  | removed
  </code></pre>
  
  <p>Expected output:-</p>
  
  <pre><code>id  |   key    |    date     |  hour    | pid  | status
  --------------------------------------------------------
  id1 |   one    |    20180619 |  4       |  p1  | added
  id1 |   one    |    20180619 |  5       |  p3  | added
  id2 |   one    |    20180619 |  5       |  p2  | added
  </code></pre>
  
  <p>I don't want to delete the data from the source table.
  I want to query the source table to produce the above result  using self join.</p>