SQL lookup key defined by LAG function

Question

I want to join two tables on a key based on LAG function. My query doesn't work though. I get an error:

Msg 4108, Level 15, State 1, Line 13 Windowed functions can only appear in the SELECT or ORDER BY clauses.

I shall appreciate any suggestion on how to tackle it.

**Table A**
Key
1
2
3
and so on...

**Table B**
MaxKey | Something
3 | A
5 | B
8 | C

**Expected Results**
Key|Something
1 A
2 A
3 A
4 B
5 B
6 C

SELECT
   tabA.Key
  ,tabB.[Something]
  ,LAG (tabB.MaxKey,1,1) OVER (ORDER BY tabB.MaxKey) AS MinKey
  ,tabB.[MaxKey] 
FROM TableA as tabA 
LEFT JOIN TableB as tabB 
ON tabA.Key > tabB.MinKey AND tabA.Key <= tabB.MaxKey

Answer 1

I think you can solve this using an outer apply like this:

select * from TableA a
outer apply (
  select top 1 something 
  from TableB b 
  where b.maxkey >= a.[key]
) oa

Sample SQL Fiddle

Another option is to modify your query to do the lag in a derived table, I believe this might work too:

SELECT
   tabA.[Key]
  ,tabB.[Something]
  ,MinKey
  ,tabB.[MaxKey] 
FROM TableA as tabA 
LEFT JOIN (
    SELECT 
       [Something]
       ,LAG (MaxKey,1,1) OVER (ORDER BY MaxKey) AS MinKey
       ,[MaxKey] 
    FROM TableB) tabB
ON tabA.[key] >= tabB.MinKey AND tabA.[key] <= tabB.MaxKey
ORDER BY tabA.[key]