Partition By Scan To Seek-CodePudding

I have a T1 table. For every value in column C1/C2, column C3 starts at 1.

CREATE TABLE t1 
(
    C1 int, 
    c2 int, 
    c3 int, 

    CONSTRAINT [pk_idx] PRIMARY KEY CLUSTERED ([c1] ASC, [c2] ASC, [c3] ASC) 
)

SELECT C1, C2, MAX(C3) AS MAX_C3 
FROM T1 
GROUP BY C1, C2

C1  C2  MAX_C3
--------------
1   1   46
1   2   540
1   3   6
1   4   2
1   5   579
2   1   46
2   2   540
2   3   6
2   4   2
2   5   579

This query retrieves all counts of C1 = 1. (number of rows read 1173).

I wonder if there is a direction where only 5 can be read.

SELECT 
    c1, c2, c3
FROM 
    (SELECT 
         c1, c2, c3,
         ROW_NUMBER() OVER (PARTITION BY [c2] ORDER BY [C3] DESC) AS TopRow
     FROM t1
     WHERE c1 = 1) R
WHERE 
    R.TopRow = 1

c1  c2  c3
-------------
1   1   46
1   2   540
1   3   6
1   4   2
1   5   579

I've been thinking about the MAX function, GROUP BY clause, or a subquery, but I can't find an answer.

CodePudding user response：

yes this is possible using a recursive CTE - e.g. as here

Unless you have a lot of rows per c1, c2 combination you may well find that just scanning all of them performs better though.