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

Jim Hubbard wrote:

Steve Guest 3. ## 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.

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

David Guest 5. ## 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.

"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. ## Re: Help with complex SQL statement?

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

Bob Guest 7. ## 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
------------ 