Multiple T-SQL Nested While Loops

Question

This T-SQL is intended to write to a table ([dbo].[unique_combinations]) all the possible values of the draw 6, 1 - 53 lottery. It starts out 1,2,3,4,5,6; 1,2,3,4,5,7... but it will only produce the last two columns (draw05 and draw06). Apparently NO ONE has EVER done this before and I am terminally unique. I cannot find an example of even 3 nested While Loop statements, much less 6. Please take a look at this code and tell me what I am dreadfully missing.

TIA -davlyo

DECLARE @start01 AS INT = 1
DECLARE @start02 AS INT = 2
DECLARE @start03 AS INT = 3
DECLARE @start04 AS INT = 4
DECLARE @start05 AS INT = 5
DECLARE @start06 AS INT = 6

DECLARE @draw01 AS INT = @start01
DECLARE @draw02 AS INT = @start02
DECLARE @draw03 AS INT = @start03
DECLARE @draw04 AS INT = @start04
DECLARE @draw05 AS INT = @start05
DECLARE @draw06 AS INT = @start06

TRUNCATE TABLE [dbo].[unique_combinations]

WHILE @draw01 <= 48
BEGIN 
    WHILE @draw02 <= 49
    BEGIN
        WHILE @draw03 <= 50
        BEGIN
            WHILE @draw04 <= 51
            BEGIN
                WHILE @draw05 <= 52
                BEGIN
                    WHILE @draw06 <= 53
                    BEGIN
                        INSERT INTO [dbo].[unique_combinations]
                                    ([draw01]
                                    ,[draw02]
                                    ,[draw03]
                                    ,[draw04]
                                    ,[draw05]
                                    ,[draw06])
                                VALUES
                                    (@draw01
                                    ,@draw02
                                    ,@draw03
                                    ,@draw04
                                    ,@draw05
                                    ,@draw06)

                SET @draw05 = @draw05 + 1
                END 
                SET @start05 += 1
                SET @draw05 = @start05

            SET @draw04 = @draw04 + 1
            END
            SET @start04 = @start04 + 1
            SET @draw04 = @start04

        SET @draw03 = @draw03 + 1
        END
        SET @start03 = @start03 + 1
        SET @draw03 = @start03

    SET @draw02 = @draw02 + 1
    END
    SET @start02 = @start02 + 1
    SET @draw02 = @start02

SET @draw01 = @draw01 + 1
END
SET @start01 = @start01 + 1
SET @draw01 = @start01

"Apparently NO ONE has EVER done this before and I am terminally unique." - might be a reason for that....What problem are you actually trying to solve? — Mitch Wheat
– Mitch Wheat, Commented Oct 31, 2018 at 1:56
if you want to have a good performance please do not do that. Sql is not a programming language, data is seen as a whole and does not interact in while or for loops like python or another programming languages — Alvaro Parra
– Alvaro Parra, Commented Oct 31, 2018 at 1:57
Do you intend to eliminate duplicates where the same numbers are in a different order? For example, if one combination is (15,16,17,18,19,20), do you also want a row for (20,19,18,17,16,15), and ( 17,19,15,20,16,18)? — David Dubois
– David Dubois, Commented Oct 31, 2018 at 2:44

Squirrel · Accepted Answer · 2018-10-31 05:46:59Z

I am too lazy to understand and debug your while loop.

If you don't mine a set-base solution, here i am using a recursive cte to simulate the number table if you don't have a number table.

; with num as
(
    select  n = 1 
    union all
    select  n = n + 1
    from    num
    where   n < 53
)
select  *
from    num n1
        cross join num n2
        cross join num n3
        cross join num n4
        cross join num n5
        cross join num n6
where   n1.n    < n2.n
and     n2.n    < n3.n
and     n3.n    < n4.n
and     n4.n    < n5.n
and     n5.n    < n6.n

This will generates 22,957,480 rows.

Tim · Accepted Answer · 2018-10-31 02:32:22Z

0

Looks like you are incrementing draw05 instead of draw06 and creating an infinite loop in your inner most WHILE @draw06 <= 53 block...

But, at first glance you have an almost infinite loop by design anyway if you are expecting to insert ~50 to 6th power of records into a table. Is that really what you are trying to do?

answered Oct 31, 2018 at 2:32

Tim

6,0681 gold badge16 silver badges19 bronze badges

Comments

LordBaconPants · Accepted Answer · 2018-10-31 02:44:18Z

The power of SQL is to do things in sets. To achieve what you are trying to do I would do this:

-- house keeping
IF OBJECT_ID( 'tempdb..#numbers' ) IS NOT NULL
BEGIN
    DROP TABLE #numbers;
END;

IF OBJECT_ID( 'tempdb..#unique_combinations' ) IS NOT NULL
BEGIN
    DROP TABLE #unique_combinations;
END;


-- create a table that has the number of numbers we want. I've used 4 just to prove
-- that it works. 53 takes up a massive amount of space and time I don't really have.
-- This is how I go about making tables of numbers. Any method will do, we
-- just want a table with a single column with the number of records we want.

SELECT  TOP (4)
    IDENTITY(INT, 1, 1) AS number
INTO    #numbers
FROM
    sys.objects            s1 
    CROSS JOIN sys.objects s2;  

--Cross join our number table to itself to create the number of required draws.
 SELECT draw1.number AS draw01
      , draw2.number AS draw02
      , draw3.number AS draw03
      , draw4.number AS draw04
      , draw5.number AS draw05
      , draw6.number AS draw06
      INTO  #unique_combinations
FROM
    #numbers            draw1
    CROSS JOIN #numbers draw2
    CROSS JOIN #numbers draw3
    CROSS JOIN #numbers draw4
    CROSS JOIN #numbers draw5
    CROSS JOIN #numbers draw6;

SELECT *  FROM #unique_combinations ;

My 4 numbers gives me 4096 rows, which is 4^6, so I'm happy with that. 53^6 is 22164361129, which is a few more.

If your question was more about how to do while loops instead of how to generate that data then please let me know.

My question is is more how to do the while looks nested... I can figure out the rest.
ok, copied and pasted the Code you provided me in your eloquent explanation, did you run your own code? The first line is 1,1,1,1,1,1; the second line is 1,2,1,1,1,1 -neither one of those line comes close to a combination. Let me think how to better articulate my question...
I did run it, yes. It will produce every combination for the value passed in. I didn't do any ordering, if that is the issue? What exactly were you after? I may have misinterpreted what you were after

davlyo · Accepted Answer · 2018-11-04 00:26:42Z

Solved this problem -I wasn't resetting the draw variables back to 1. I really feel I did this with as little code as necessary. First -I shouldn't have started working on this or any project on a whim at 23:00. Thank you for all the assistance -everyone's input was used in finally figuring the solution.

    WHILE @draw01 <= 48
    BEGIN
        WHIle @draw02 <= 49
        BEGIN
            WHILE @draw03 <= 50
            BEGIN
                WHILE @draw04 <= 51
                BEGIN
                    WHILE @draw05 <= 52
                    BEGIN
                        WHILE @draw06 <= 53
                        BEGIN   
                            INSERT INTO [dbo].[unique_combinations]
                                        ([draw01], [draw02], [draw03], [draw04], [draw05], [draw06])
                                    VALUES
                                        ( @draw01,  @draw02,  @draw03,  @draw04,  @draw05, @draw06)
                        SET @draw06 += 1
                        END
                        SET @draw06 = @draw05 + 2

                    SET @draw05 += 1
                    END
                    SET @draw05 = @draw04 + 1

                SET @draw04 += 1
                END
                SET @draw04 = @draw03 + 1

            SET @draw03 += 1
            END 
            SET @draw03 = @draw02 + 1

        SET @draw02 += 1
        END
        SET @draw02 = @draw01 + 1

    SET @draw01 += 1
    END

Collectives™ on Stack Overflow

Multiple T-SQL Nested While Loops

4 Answers 4

Comments

Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Related