SELECT
  Name,
  Number,
  Revision,
  Status  
FROM
  Document
WHERE
  (Status = 'Pending Effective') OR
  (
    (Status = 'Effective') AND
    NOT EXISTS (
      SELECT * FROM Document AS d WHERE (Document.Number = d.Number) AND
      (d.Status = 'Pending Effective')
    )  
  )