Professional Web Applications Themes

Help with complex SQL statement? - Microsoft SQL / MS SQL Server

Hello Jim ! Do you want the query result for those combinations, so you got three numbers and want the rest of the combinations possible ? Or do you just want the number of possible combinations, these numbers won´t change, but this questions would be worth a post in a mathematical NG ;-) (Its long ago that it calculated this, something to do with the !-Funktion) HTH,Jens Süßmeyer. "Jim Hubbard" <net> schrieb im Newsbeitrag news:c302b.7473$atl2.webusenet.com...  The  the  chosen.)  order ...

  1. #1

    Default Re: Help with complex SQL statement?

    Hello Jim !

    Do you want the query result for those combinations, so you got three
    numbers and want the rest of the combinations possible ? Or do you just want
    the number of possible combinations, these numbers won´t change, but this
    questions would be worth a post in a mathematical NG ;-) (Its long ago that
    it calculated this, something to do with the !-Funktion)

    HTH,Jens Süßmeyer.


    "Jim Hubbard" <net> schrieb im Newsbeitrag
    news:c302b.7473$atl2.webusenet.com... 
    The 
    the 
    chosen.) 
    order 


    Jens Guest

  2. #2

    Default Re: Help with complex SQL statement?

    [replied only to microsoft.public.sqlserver.programming.
    Other groups are not official MS groups]

    Jim,

    It sounds like you are asking for two different things: one is a
    query to retrieve rows representing drawings that have taken place
    and one is to pick "all possible outcomes," or the number of
    of possible outcomes.

    Here's an example of how you might do the first of those. It's not
    going to be efficient for large amounts of data, however.

    create table Drawings (
    DrawNum int identity(1,1),
    Ball1 varchar(5),
    Ball2 varchar(5),
    Ball3 varchar(5),
    Ball4 varchar(5),
    Ball5 varchar(5)
    )
    go

    insert into Drawings values (12,33,23,34,9)
    insert into Drawings values (46,21,7,13,22)
    insert into Drawings values (21,7,9,13,36)
    insert into Drawings values (13,12,11,4,3)
    go

    create function RequiredTable(
    r varchar(25)
    -- required balls **right-justified in
    -- 5-character fields**
    ) returns table as return
    select ltrim(R) as R from (
    select substring(r,1,5) as R union all
    select substring(r,6,5) union all
    select substring(r,11,5) union all
    select substring(r,16,5) union all
    select substring(r,21,5)
    ) T where len(R) > 0
    go


    declare required varchar(25)
    set required = ' 13 7'
    -- format is BALL1BALL2...

    select * from Drawings
    where DrawNum in (
    select DrawNum
    from Drawings, RequiredTable(required) RT
    where R in (Ball1,Ball2,Ball3,Ball4,Ball5)
    group by DrawNum
    having count(R) = len(required)/5
    )
    go

    drop table Drawings
    drop function RequiredTable
    go

    -- Steve Kass
    -- Drew University
    -- Ref: D2020AA6-3A79-4DF3-ADE6-593EFA3E43C5

    Jim Hubbard wrote:
     

    Steve Guest

  3. #3

    Default Re: Help with complex SQL statement?

    The number of all possible combination using factoring would be 18,424 or
    (49*48*47)/(3*2*1) which calculates the number of possible combinations.

    But, what I need is the actual listing of the combinations that are
    currently in the database that contain a subset submitted to a stored
    procedure. There is no INSTRING function like in vb, that I can find.

    You can do a "Select * from Drawings where Ball1 IN ('17', '32', '42')", but
    taking all possible combinations of the 5 possible spaces and three numbers
    gives 10 possible combinations.

    Perhaps the the answer is to calculate the possible 10 combinations first,
    then pass those to a stored proc and do a simple select for each one. Doing
    this simply means that I need a seperate stored proc for (or a single stored
    proc that has different logic for) each possibility of 1, 2, 3, 4, or 5
    balls in the known subset.

    Thanks for your reply!


    "Jens Süßmeyer" <jsuessmeyer[REJECT_SPAM]web.de> wrote in message
    news:phx.gbl... 
    want 
    that 
    > The 
    > the [/ref]
    the [/ref]
    be 
    > chosen.) [/ref]
    42, [/ref]
    Ball3=?, 
    > order 
    >
    >[/ref]



    Jim Guest

  4. #4

    Default Re: Help with complex SQL statement?

    Assuming the drawn order has no significance, then it makes more sense to
    put your draw in a single column table:

    CREATE TABLE Balls (ball INTEGER PRIMARY KEY CHECK (ball BETWEEN 1 AND 49))

    What you are really after is a subset of the unique combinations of balls
    (total 49 * 48 * 47 * 46 * 45 = 228,826,080) containing the required balls.
    Try this:

    First populate the table with the full set of balls:

    WHILE (SELECT COALESCE(MAX(ball),0) FROM Balls)<49
    INSERT INTO Balls
    SELECT COALESCE(MAX(ball),0)+1 FROM Balls

    Define the subset you want:

    CREATE TABLE BallSubset (ball INTEGER PRIMARY KEY CHECK (ball BETWEEN 1 AND
    49))
    INSERT INTO BallSubset VALUES (17)
    INSERT INTO BallSubset VALUES (32)
    INSERT INTO BallSubset VALUES (42)

    Now pull each combination containing those balls (2070 rows in total)

    SELECT *
    FROM
    (SELECT MIN(ball),
    (SELECT MIN(ball)
    FROM BallSubset
    WHERE ball>
    (SELECT MIN(ball)
    FROM BallSubset)),
    MAX(ball)
    FROM BallSubset) AS B1 (b1,b2,b3)
    CROSS JOIN
    (SELECT B1.ball, B2.ball
    FROM Balls AS B1
    JOIN Balls AS B2
    ON B1.ball<>B2.ball
    WHERE b1.ball NOT IN (SELECT ball FROM BallSubset)
    AND b2.ball NOT IN (SELECT ball FROM BallSubset)) AS B2 (b4,b5)

    Remember the draw is an unordered set, the actual ordering here is
    insignificant.

    Finding winning tickets is easier with the above scheme than in your design:

    CREATE TABLE Tickets (ticketno INTEGER NOT NULL, ball INTEGER NOT NULL CHECK
    (ball BETWEEN 1 AND 49), PRIMARY KEY (ticketno,ball))

    CREATE TABLE Draw (ball INTEGER PRIMARY KEY CHECK (ball BETWEEN 1 AND 49))

    SELECT ticketno AS jackpot_winner
    FROM Tickets AS T
    JOIN Draw AS D
    ON T.ball = D.ball
    GROUP BY ticketno
    HAVING COUNT(*)=5

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  5. #5

    Default Re: Help with complex SQL statement?

    Thanks for the pointers Bob!

    When I looked up PATINDEX, I also found CHARINDEX (accepts only literal
    strings - no wildcards like in PATINDEX). In my table (currently over 2,5
    million records), PATINDEX for one ball took 1:54 whereas CHARINDEX took
    only 0:29.

    Seems the wildcard (%) is a real killer when querying a lot of records.

    I have a target goal of 20 seconds or less.

    Thanks for your help!

    "Bob Barrows" <com> wrote in message
    news:phx.gbl... 
    >
    > CATINDEX and PATINDEX are the T-SQL versions of InStr (which is what I
    > presume you meant by "INSTRING")
    >
    > Bob
    >
    >[/ref]



    Jim Guest

  6. #6

    Default Re: Help with complex SQL statement?

    Jim Hubbard wrote: 
    Oops - I meant to say CHARINDEX ...


    Bob Guest

  7. #7

    Default Re: Help with complex SQL statement?

    The problem is in your design. Having a column for each ball in the sequence
    makes the query very difficult. See my earlier reply.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

Similar Threads

  1. Complex SQL?
    By Toni Van Remortel in forum MySQL
    Replies: 5
    Last Post: December 7th, 06:08 PM
  2. complex SELECT statement
    By mfitz in forum Coldfusion - Advanced Techniques
    Replies: 9
    Last Post: August 25th, 06:50 PM
  3. Complex situation
    By Mike in forum ASP Database
    Replies: 1
    Last Post: December 30th, 04:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139