title: SQL Server cross apply not working?
body: <p><a href="http://sqlfiddle.com/#!3/78273/1" rel="noreferrer">http://sqlfiddle.com/#!3/78273/1</a></p>
  
  <pre><code>create table emptb1
  (
  id int,
  name varchar(20),
  dept int
  )
  
  insert into emptb1 values (1,'vish',10);
  insert into emptb1 values (2,'vish',10);
  insert into emptb1 values (3,'vish',30);
  insert into emptb1 values (4,'vish',20);
  
  create table depttb1
  (
  id int,
  name varchar(20)
  )
  
  insert into depttb1 values(10,'IT')
  insert into depttb1 values(20,'AC')
  insert into depttb1 values(30,'LIC')
  
  select * from emptb1
  
  select e.id, e.name, a.id
  from emptb1 e
  cross apply
  (
  select top 1 * from depttb1 d
  where d.id = e.dept
  order by d.id desc
  ) a
  </code></pre>
  
  <p>I was trying to learn cross apply as it's similar as inner join but works with function.</p>
  
  <p>In above query I'm assuming it should take only dept=30 because order d.id desc will give only top 1st id which is 30 and then it should return employees with dept id = 30 but it's giving me all the rows and all the deptid.</p>
  
  <p>What's wrong with query or I'm wrong interpreting the concept of cross apply.</p>