Converting Cursor based filtering logic to CTE based

I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition where I have to get the top 10 values for each iteration in cursor which I am not able to do with set based logic.

Assuming the cursor based logic being:

SET @mycurse = CURSOR FOR
SELECT value FROM sometable

OPEN @mycurse

    FETCH NEXT FROM @mycurse INTO @SomeVariable

    IF @@FETCH_STATUS <> 0 
        Print 'Error in the Cursor Fetch Statement'

    WHILE @@FETCH_STATUS = 0    

    BEGIN

INSERT INTO @TOP10      
    SELECT Value1,Value2,Value3,Value4
        FROM (
                select Value1,Value1,Value3,Value4 from BaseTable where SomeVariable=@SomeVariable
        )           

        FETCH NEXT FROM @mycurse INTO @SomeVariable

    END 

CLOSE @mycurse

And the CTE based soltion looks like this:

;With myCTE(value)
as
(
    SELECT value FROM sometable
)
INSERT INTO @TOP10      
    SELECT Value1,Value2,Value3,Value4
    FROM 
      (select 
          Value1, Value1, Value3, Value4 
       from BaseTable BT, myCTE MC 
       where BT.SomeVariable = MC.SomeVariable)

As you see both work fine and return identical result, with CTE version using significantly less time. But the logic of having top 10 values makes it go wrong, it returns only 10 values where it is supposed to return 20 values.

```
SET @mycurse = CURSOR FOR
SELECT value FROM sometable

OPEN @mycurse

FETCH NEXT FROM @mycurse INTO @SomeVariable

IF @@FETCH_STATUS <> 0
Print 'Error in the Cursor Fetch Statement'

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @TOP10
SELECT top 10 Value1,Value2,Value3,Value4
FROM
(select
Value1, Value1, Value3, Value4
from BaseTable
where SomeVariable = @SomeVariable)

FETCH NEXT FROM @CUR_Liability INTO @VarLiability

END

CLOSE @CUR_Liabi

If I understand correctly, and you want to insert 10 values of the 2nd table for every iteration of the cursor (or cte), you can use this:

; WITH myCTE (value) AS
    ( SELECT value FROM sometable )
INSERT INTO @TOP10      
    SELECT t.Value1, t.Value2, t.Value3, t.Value4
    FROM myCTE AS mc
      CROSS APPLY 
        ( SELECT TOP (10)
              bt.Value1, bt.Value2, bt.Value3, bt.Value4
          FROM BaseTable AS bt 
          WHERE bt.SomeVariable = mc.value
          ORDER BY                      -- needs something here
        ) AS t ;

You also need an ORDER BY in the subquery with TOP to be consistent. Without order by, the database engine is free to return any 10 rows that match the conditions.

It could also be done using ROW_NUMBER():

; WITH myCTE (Value1, Value2, Value3, Value4) AS
    ( SELECT bt.Value1, bt.Value2, bt.Value3, bt.Value4,
             Rn = ROW_NUMBER() OVER (PARTITION BY bt.SomeVariable
                                     ORDER BY )
      FROM sometable AS s
        JOIN BaseTable AS bt 
          ON bt.SomeVariable = s.value
    )
INSERT INTO @TOP10      
    SELECT Value1, Value2, Value3, Value4
    FROM myCTE 
    WHERE Rn <= 10 ;

StackExchange Database Administrators Q#76383, answer score: 4