title: Why FIRST_VALUE and EXISTS statement in WHERE clause doesn't filter rows?
body: <p>In the output of the below sample I would expect to see a single row with ControlNo = 2222.</p>
  <p>But why it still brings me 2 rows?</p>
  <p><strong>TEST data</strong></p>
  <pre><code>IF OBJECT_ID('tempdb..#pol') IS NOT NULL DROP TABLE #pol;
  select 1111 as ControlNo, 'Policy1' as PolicyNumber, 'Cancelled' as CurrStatus, 0 as Premium
  into #pol
  union 
  select 2222 as ControlNo, 'Policy1' as PolicyNumber, 'Bound' as CurrStatus, 100 as Premium
  --select * from #pol
  
  select *
  from #pol a
  where PolicyNumber = 'Policy1'
  -- here I want to filter only  ControlNo 2222
  and exists(
  SELECT FIRST_VALUE(ControlNo) OVER (PARTITION BY PolicyNumber ORDER BY ControlNo DESC) 
  FROM #pol p
  WHERE p.ControlNo = a.ControlNo
  )
  </code></pre>
  <p>Otput:</p>
  <pre><code>ControlNo   PolicyNumber    CurrStatus  Premium
    1111     Policy1          Cancelled     0
    2222     Policy1           Bound       100
  </code></pre>
  <p>If I run just FIRST_VALUE statement then it works as expected. Gave me the last <code>ControlNo</code>, which is 2222.</p>
  <pre><code>SELECT FIRST_VALUE(ControlNo) OVER (PARTITION BY PolicyNumber ORDER BY ControlNo DESC) 
  FROM #pol p
  
  (No column name)
      2222
      2222
  </code></pre>
  <p>But if I place this statement in EXISTS clause then it brings me both <code>ControlNo</code>'s</p>