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

1

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.

answered Oct 31, 2018 at 5:46

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

0

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.

edited Oct 31, 2018 at 2:44

answered Oct 31, 2018 at 2:31

LordBaconPants

1,4241 gold badge20 silver badges23 bronze badges

3 Comments

davlyo Over a year ago

My question is is more how to do the while looks nested... I can figure out the rest.

davlyo Over a year ago

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...

LordBaconPants Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related