How to perform reference a window function inside current table?

Question

I have this part in a larger query which consume lot of RAM:

TopPerPost as
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id (The most common close Reason)
   from ReasonsPerPost
   where Name is NOT NULL and TopPerPost.seq=1 -- Remove useless results here, instead of doing it later
)

but I got The multi-part identifier "TopPerPost.seq" could not be bound.
Last detail... I only Use theNamecolumn in a laterINNER JOINof that table.

Answer 1

You can't reference a window function in the where of the same query. Just create a second cte.

with TopPerPost as
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id
   from ReasonsPerPost
   where Name is NOT NULL 
)
, OnlyTheTop as
(
    select *
    from TopPerPost
    where seq = 1
)

Or you can do it like this.

select * from 
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id
   from ReasonsPerPost
   where Name is NOT NULL 
) s
where seq = 1

Here is another option that should eliminate the need for so many rows being returned.

select Id,
      CloseReasonTypeId,
      Name,
      s.TotalByCloseReason
   from ReasonsPerPost rpp
   cross apply
   (
        select top 1 TotalByCloseReason
        from ReasonsPerPost rpp2
        where rpp2.Id = rpp.Id
        order by TotalByCloseReason desc
   ) s
   where Name is NOT NULL 

Attempt #4...this would be a LOT easier with a sql fiddle to work with.

select Id,
      CloseReasonTypeId,
      Name,
      s.TotalByCloseReason
   from ReasonsPerPost rpp
   inner join
   (
        select top 1 TotalByCloseReason
        from ReasonsPerPost rpp2
        where rpp2.Id = rpp.Id
        and Name is NOT NULL
        order by TotalByCloseReason desc
   ) s on s.Id = rpp.Id
   where Name is NOT NULL

Answer 2

The below might work for your need. But without looking at the data is hard to tell it will or not.

;with t as
(
  Select Id, max(totalbyclosereason) TC from reasonsperpost where name is not null group by id
)
Select T.id,t.tc,c.closereasontypeid,c.name 
From t join reasonsperpost c on t.id = c.id and t.tc = c.totalbyclosereason