http://sqlfiddle.com/#!3/78273/1
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
I was trying to learn cross apply as it's similar as inner join but works with function.
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.
What's wrong with query or I'm wrong interpreting the concept of cross apply.