How can I use COUNT() result in RANK-CodePudding

I have a SQL query for 'SQL Server' which 'count()' based on column 'id'. And I also want to 'RANK' based on the value of COUNT(). But when I try the below query, I get error sayign 'Invalid column names 'IdCount''

SELECT 
[id],
COUNT(*) AS IdCount,
RANK() OVER ( 
   ORDER BY IdCount
) CountRank
  FROM  myTable
  where DATEDIFF(day,[Time],GETDATE()) < 30
  GROUP BY [id]

Can you please tell me how can I reference the COUNT() result?

Thank you.

CodePudding user response：

You can't reference an expression's alias in a window function at the same scope. Also, I think you want the RANK() to be applied from the highest count, not the lowest. Finally, you should never apply calculations like DATEDIFF against a column. How about:

DECLARE @start date = DATEADD(DAY, -30, GETDATE());

SELECT id, IdCount,
  CountRank = RANK() OVER (ORDER BY IdCount DESC)
FROM 
(
  SELECT id, COUNT(*)
    FROM dbo.myTable
    WHERE [Time] >= @start
    GROUP BY id
) AS x;