# Better approach to optimize query

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.

• @ypercubeᵀᴹ Imagine that the maximum of COL2 corresponds to 20 different COL1. Which of them will fall into the CTE1? Sep 10, 2018 at 18:59
• @Akina yes, that makes sense. In that case, CTE1 does not return completely determinate results. Sep 11, 2018 at 11:26

As I understand your question (and let me know if I have misunderstood your requirements):

1. Using the TOP 10 rows from Table1 where they fall into some date criteria and are ordered by `COL2`
2. Get the `MAX(COL2)` per `COL1` group
3. Then, select all columns from Table2 where `IDCOL` = `MAX(COL2)` from the previous grouping.

I think this solution addresses all of those items. Basically, `CTE2` is encapsulated into `CTE1`.

``````--Demo setup
Declare @Table1 TABLE (COL1 INT, COL2 INT)
INSERT INTO @Table1(COL1, COL2) VALUES

(10005,500),
(10005,501),
(10005,521),
(10005,521),

(42344,502),
(42344,550),

(12345,503),
(12345,575),
(12345,575)

Declare @Table2 TABLE (IDCOL INT,OtherStuff varchar(10))
INSERT INTO @Table2(IDCOL,OtherStuff) VALUES
(575,'Other1'),(575,'Other2'),(580,'Other3')

--The solution
;WITH CTE1 (COL1,COL2,Rn)
AS (
SELECT TOP 10 COL1
,COL2
,ROW_NUMBER() OVER (
PARTITION BY COL1 ORDER BY COL2 DESC
) AS Rn
FROM @Table1
--   WHERE CREATEDATE < DATEADD(HOUR, -1, GETDATE())
ORDER BY COL2
)
--select * from CTE1 order by col1, rn
SELECT T2.*
FROM CTE1 c1
JOIN @Table2 T2
ON t2.IDCOL = c1.COL2
AND c1.Rn = 1
``````

Rows coming out of `CTE1` have an additional `rn` column.

``````| COL1  | COL2 | Rn |
|-------|------|----|
| 10005 | 521  | 1  |
| 10005 | 521  | 2  |
| 10005 | 501  | 3  |
| 10005 | 500  | 4  |
| 12345 | 575  | 1  |
| 12345 | 575  | 2  |
| 12345 | 503  | 3  |
| 42344 | 550  | 1  |
| 42344 | 502  | 2  |
``````

The last part of the query joins `Table2` with the `CTE1` where only the rows from `CTE1` have `rn` = 1 and `Table2` (IDCOL) = `COL2` value for `rn` = 1.

``````| IDCOL | OtherStuff |
|-------|------------|
| 575   | Other1     |
| 575   | Other2     |
``````

Ultimately, you will have to judge whether it is a Better approach to optimize query.