DECLARE @t TABLE (strcol CHAR(3));DECLARE @ValueOfInterest INT = 10;INSERT INTO @t (strcol)SELECT ' 5' UNION ALL SELECT ' 6' UNION ALL SELECT ' 3'UNION ALL SELECT ' 4' UNION ALL SELECT ' 3';-- Improved Combinations;WITH UNIQUEnTuples (n, Tuples, ID, CSum) AS ( SELECT DISTINCT 1, CAST(strcol AS VARCHAR(8000)), strcol, CAST(strcol AS INT) FROM @t UNION ALL SELECT 1 + n.n, t.strcol + ',' + n.Tuples, strcol, CSum+CAST(t.strcol AS INT) FROM UNIQUEnTuples n CROSS APPLY ( SELECT strcol FROM @t t WHERE t.strcol < n.ID) t )SELECT TOP 1 Tuples, SumOfTuples=CSumFROM UNIQUEnTuplesWHERE n <= 2 AND CSum <= @ValueOfInterestORDER BY CSum DESC;

WHERE n <= 3 AND CSum BETWEEN @ValueOfInterest - 1 AND @ValueOfInterest + 1

DECLARE @t TABLE (strcol CHAR(4));DECLARE @ValueOfInterest INT = 100;INSERT INTO @t (strcol)--SELECT ' 5' UNION ALL SELECT ' 6' UNION ALL SELECT ' 3'--UNION ALL SELECT ' 4' UNION ALL SELECT ' 3';SELECT TOP 100 RIGHT('000' + CAST(ABS(CHECKSUM(NEWID())) % 200 AS VARCHAR(3)), 4)FROM sys.all_columns; SET STATISTICS TIME ON;-- Improved CombinationsWITH UNIQUEnTuples (n, Tuples, ID, CSum) AS ( SELECT DISTINCT 1, CAST(strcol AS VARCHAR(8000)), strcol, CAST(strcol AS INT) FROM @t UNION ALL SELECT 1 + n.n, t.strcol + ',' + n.Tuples, strcol, CSum+t.strcol FROM UNIQUEnTuples n CROSS APPLY ( SELECT strcol FROM @t t WHERE t.strcol < n.ID) t WHERE CSum+t.strcol <= @ValueOfInterest )SELECT TOP 5 Tuples, SumOfTuples=CSumFROM UNIQUEnTuplesWHERE n <= 3 AND CSum <= @ValueOfInterestORDER BY CSum DESCOPTION (RECOMPILE);SET STATISTICS TIME OFF;

DECLARE @t TABLE (strcol CHAR(3));DECLARE @ValueOfInterest INT = 10;INSERT INTO @t (strcol)SELECT ' 5' UNION ALL SELECT ' 6' UNION ALL SELECT ' 3'UNION ALL SELECT ' 4' UNION ALL SELECT ' 3';-- Improved Combinations;WITH UNIQUEnTuples (n, Tuples, ID, CSum) AS ( SELECT DISTINCT 1, CAST(strcol AS VARCHAR(8000)), strcol, CAST(strcol AS INT) FROM @t UNION ALL SELECT 1 + n.n, t.strcol + ',' + n.Tuples, strcol, CSum+CAST(t.strcol AS INT) FROM UNIQUEnTuples n CROSS APPLY ( SELECT strcol FROM @t t WHERE t.strcol < n.ID) t )SELECT DISTINCT Tuples, SumOfTuples=CSumFROM UNIQUEnTuplesWHERE n <= 2 AND CSum = (SELECT MAX(CSum) MS FROM UNIQUEnTuples WHERE n <= 2 AND CSum < @ValueOfInterest)ORDER BY CSum DESC;