This is my query

    select * from [database].[W_work]
 where DATE__CALC is null and ST_ID = 86
 group by ST_ID 
 having MAX(modification_date) < GETDATE()

I wanna avoid group by in my query. How can i do it please ? Thank you

CodePudding user response：

I suspect what you actually need here is a windowed aggregate and a CTE or derived table:

WITH CTE AS(
    SELECT {Column List}, --Don't use *, define your columns
           MAX(modification_date) OVER (PARTITION BY ST_ID) AS Max_modification_date
    FROM [database].[W_work] --Do you really have a schema called "database"?
    WHERE DATE__CALC IS NULL
      AND ST_ID = 86)
SELECT {Column List} --Don't use *, define your columns
FROM CTE
WHERE Max_modification_date < GETDATE();