Why isn't my left outer join working?

See more:

T-SQL

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(--Gets one result a day so averages later are not oddly waited when two runs occurred in a single day
		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
) -- adds the row_number as num
	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 -- change this to 1 and all results return