Converting Cursor based filtering logic to CTE based

Question

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_Liability

So please provide me with a solution for this scenario, it does not necessarily need to be CTE based my goal is to reduce run time. Also I can update with sample result set if this is not clear enough. Thanks in advance.

Update : I have managed to cook up a solution using Row_Number() function but I am still curious about other options.

Answer 1

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 <SomeColumns>                     -- 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 <SomeColumns>)
      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 ;