Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Outer Apply successer cell chek and get the result
create table Las(name nvarchar(50), Depth int); insert into Las values('testName1', 25); insert into Las values('testName2', 76); insert into Las values('testName3', 31); insert into Las values('testName4', 24); create table Calci(name nvarchar(50), Depth int, carbon int, calcium int); insert into Calci values('testName1', 24,30,10); insert into Calci values('testName1', 30,25,15); insert into Calci values('testName1', 31,24,16); insert into Calci values('testName2', 75,25,15); insert into Calci values('testName2', 80,24,16); insert into Calci values('testName2', 85,28,25); create table Drill(name nvarchar(50), Depth int, AHD int, PHD int); insert into Drill values('testName1', 24,15,17); insert into Drill values('testName1', 30,99,88); insert into Drill values('testName1', 31,24,16); insert into Drill values('testName2', 75,25,15); insert into Drill values('testName2', 80,30,60); insert into Drill values('testName2', 85,28,25); insert into Drill values('testName3', 30,90,80); insert into Drill values('testName3', 35,66,77); select * from Las select * from Calci select * from Drill select t1.name,t1.Depth,q.carbon,q.calcium, D.AHD, D.PHD from Las t1 OUTER apply ( select top 1 carbon,calcium from Calci t2 where t2.name=t1.name and t2.Depth>t1.Depth -- From all the highest,.... order by t2.Depth asc -- get the smaller )q OUTER apply ( select top 1 AHD,PHD from Drill t2 where t2.name=t1.name and t2.Depth>t1.Depth -- From all the highest,.... order by t2.Depth asc -- get the smaller )D
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Delete double entries from a table without primary key
sql_fb
common table expression
FIGURA5.2
BT SQL Project
Left join producing wrong result
Empresa
Campeonato 3 bilhoes
Basic Except
test
Please log in to post a comment.