Hi,
This is odd. I have a number of CTE's which lead up to a list of results with a row_number. 1 is most recent, 2 is second most recent, etc.
I select the results from this table then join it again as a second table with a left outer join. I have a Where clause for each so the first selects row=1 and the second row = 2.
There are currently no results where row = 2. I expect these columns to be null
When I have just the first table, I get 66 results
When I join the second table, I get none!
What am I doing wrong?
here is the code (trimmed to the essential parts)
with daily_results as(
select
mr.[moz_result_id] as id,
mr.[moz_result_url_id] as url_id,
convert(datetime,convert(date, mr.[moz_result_datetime])) as [datetime],
AVG(cast(mr.[moz_result_page_authority] as float)) as page_authority,
AVG(cast(mr.[moz_result_domain_authority] as float)) as domain_authority,
AVG(cast(mr.[moz_result_moz_trust] as float)) as trust,
row_number() over (partition by mr.[moz_result_url_id] order by convert(datetime,convert(date, mr.[moz_result_datetime])) desc) as num
from dbo.moz_results mr
group by
mr.[moz_result_id] ,
mr.[moz_result_url_id] ,
convert(datetime,convert(date, mr.[moz_result_datetime]))
)
, moz as (
select
mr.[id]
,mr.[url_id]
,mr.[datetime]
,mr.[page_authority]
,mr.[domain_authority]
,mr.[trust]
,row_number() over (partition by mr.[url_id] order by mr.[datetime] desc) as num
from daily_results mr
)
select
mr.[id]
,mr.[url_id]
,mr.[datetime]
,mr.[page_authority]
,mr.[domain_authority]
,mr.[trust]
,prev.[page_authority] as prev_page_authority
,prev.[domain_authority] as prev_domain_authority
,prev.[trust] as prev_trust
from moz mr
left outer join moz prev on mr.url_id = prev.url_id
where
mr.num = 1
and prev.num = 2