We have a previously designed query that functions fairly well but I am thinking there has got to be a better way of performing the following. Here is the query...
WITH CTE1 ( COL1, COL2) AS (SELECT TOP 10 COL1, COL2 FROM TABLE 1 WHERE CREATEDATE < DATEADD(HOUR, -1, GETDATE()) ORDER BY COL2 ), WITH CTE2 ( COL1, COL2) AS (SELECT COL1,MAX(COL2) FROM CTE1 GROUP BY COL1 ) SELECT TABLE2.* FROM TABLE2 WHERE TABLE2.IDCOL IN (SELECT COL2 FROM CTE2)
So CTE1 can have results like this that require the grouping in CTE2.
CTE1 results look like this:
COL1 | COL22 _________________ 10005 | 500 10005 | 501 42344 | 502 12345 | 503
SO CTE2 is designed to consolidate CTE1 down to the single row by grouping to
COL1 | COL2 _________________ 10005 | 501 42344 | 502 12345 | 503 ETC.
I am trying to figure out if I can accomplish this same task without taking CTE1 into CTE2 and then using CTE2 results in the SELECT query.