SQL Server Crossroads not working?

Question

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 tried to learn cross apply, as it looks like an inner join, but works with a function.

In the above query, I assume that it should only accept dept = 30, because the order d.id desc will only give the top 1st identifier, which is 30, and then it should return employees with dept id = 30, but it gives me all the lines and all the defects.

What is wrong with the request, or am I misinterpreting the concept of the cross.

Answer 1

: " , dept = 30, d.id desc 1- , 30, dept id = 30".

, . ( ):

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

APPLY , () . , , SELECT. :

• FROM
• WHERE
• SELECT
• ORDER BY
• TOP

, TOP , WHERE. , where d.id = e.dept , d.id e.dept ( 30), , . . , 30.

, ( CROSS APPLY):

select e.id, e.name, a.id
from   emptb1 e
cross apply
(
    select top 1 * 
    from
    (
        select top 1 * 
        from depttb1 d
        order by d.id desc
    ) b
    where b.id = e.dept
) a

, , ORDER BY, TOP 1 WHERE. ( , , , , CROSS APPLY ).

Answer 2

Damien, :

select top 1 * from depttb1 d
where d.id = e.dept
order by d.id desc

:

select e.id, e.name, a.id
from emptb1 e

, . , , , , APPLY .

, , 1 10, :

select top 1 * from depttb1 d
where d.id = 10  //this is the dept id for the current row from your outer query
order by d.id desc

Answer 3

, sub-. , , , id .

-- Using a sub query to find max dept
select e.id, e.name
from emptb1 e
where e.dept in
(
select top 1 id 
from depttb1 
order by id desc
)

CROSS APPLY CROSS JOIN. . (DMV), (TVF)

, , LEFT JOIN.

select e.id, e.name
from emptb1 e
outer apply 
    (
    select top 1 d.id from depttb1 d order by d.id desc
    ) AS m (id)
where e.dept = m.id

.

CROSS APPLY - http://craftydba.com/?p=3767

- http://craftydba.com/?p=3796

(INLINE) - http://craftydba.com/?p=3733

TABLE COST FUNCTION (MULTI LINE) - http://craftydba.com/?p=3754