SQL Lottery Remove/Hide Dupes [RESOLVED]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Lottery Remove/Hide Dupes [RESOLVED]

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-11 : 10:22:03

*EDIT - RESOLVED

All,
Playing around here and trying to figure out some logic. I have 10 Balls (0-9), and 3 are drawn. I am trying to figure out the possible combinations minus 3 of a kind. I noticed two rows 012 and 021 which are the same. Trying to figure out how to show only one record instead of both. Here is the SQL I have so far.


DECLARE @Ball TABLE ( NUM INT )
INSERT INTO @Ball VALUES (1)
INSERT INTO @Ball VALUES (2)
INSERT INTO @Ball VALUES (3)
INSERT INTO @Ball VALUES (4)
INSERT INTO @Ball VALUES (5)
INSERT INTO @Ball VALUES (6)
INSERT INTO @Ball VALUES (7)
INSERT INTO @Ball VALUES (8)
INSERT INTO @Ball VALUES (9)
INSERT INTO @Ball VALUES (0)

DECLARE @Draw TABLE ( AN INT, BN INT, CN INT )
INSERT INTO @Draw
SELECT AN, BN, C.NUM CN
FROM ( SELECT A.NUM AN, B.NUM BN FROM @Ball A CROSS APPLY @Ball B WHERE A.NUM <> B.NUM ) B
    CROSS APPLY @Ball C
WHERE C.NUM <> B.AN AND C.NUM <> B.BN
ORDER BY AN, BN, CN

SELECT *
FROM @Draw

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 11:03:05

If I understood correctly....


....
SELECT AN, BN, C.NUM CN
FROM ( SELECT A.NUM AN, B.NUM BN FROM @Ball A CROSS APPLY @Ball B WHERE A.NUM <> B.NUM a.num < b.Num ) B
    CROSS APPLY @Ball C
WHERE C.NUM <> B.AN AND C.NUM <> B.BN AND b.an < c.num AND b.bn < c.num
.....

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-11 : 11:21:04

Perfect. Not sure if I understand the < and not <>.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 11:40:42

You are right. You don't need the <> conditions, because if something satisfies the less than condition, it would also satisfy the not equal to condition.

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-11 : 11:47:08

Thanks. Looking at it I visually cannot comprehend < instead of <>. <> means not equal to, like doesn't already exists. Either way, thanks again.


SELECT
    AN
    , BN
    , C.NUM CN
FROM ( 
    SELECT 
        A.NUM AN
        , B.NUM BN 
    FROM
        @Ball A
        CROSS APPLY @Ball B
    WHERE 
        A.NUM < B.NUM
    ) B
    CROSS APPLY @Ball C
WHERE
    B.AN < C.NUM
    AND B.BN < C.NUM

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-11 : 11:49:21

My next task is going to write this dynamically so I can specify how many balls to draw.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-11 : 12:16:16

If you wanted it to be truly dynamic, I don't know of a way other than to construct dynamic SQL and execute it. Constructing the query for an arbitrary number of balls follows a simple pattern as shown below, so it wouldn't be too hard to write separate queries, or to construct dynamic SQL.

-- This is for four balls.
SELECT
	d1.num,
	d2.num,
	d3.num,
	d4.num 
FROM
	@ball d1
	INNER JOIN @ball d2 ON d2.NUM > d1.NUM
	INNER JOIN @ball d3 ON d3.num > d2.NUM
	INNER JOIN @ball d4 ON d4.num > d3.NUM
ORDER BY
	1,2,3,4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources