SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 All permutations of 50 rugby matches?
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/20/2012 :  14:46:02  Show Profile
I know it's do-able in SQL, just don't know how to do it.

50 matches.

Each one can end in a home win (H), a draw (D) or an away win (A).

At it's simplest, all 50 matches end in a home win so I get 50 columns of H.

49 home wins and a draw means I get 49 columns of H and 1 of D.

What code should I use?

TIA.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/20/2012 :  14:49:34  Show Profile
how is data stored in table? or is question on table design?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Mike Jackson
Starting Member

37 Posts

Posted - 12/21/2012 :  09:55:35  Show Profile
Typically we would discourage that many columns and encourage a vertical design, adding rows for win or loss.

Mike
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  10:37:40  Show Profile
The data is notional, it doesn't exist anywhere.

It would just a theoretical question as to what the code would be (similar to a previous thread asking about every possible permutation of numbers 1 thru 10).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/21/2012 :  11:35:22  Show Profile
the solution depends on how data is stored

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  13:21:32  Show Profile
Well either as 3 records in a table (H, A, D) crossjoined to another table with 50 records (1 thru 50)......would that work?

Or as 150 records in a table (H1 thru H50, A1 thru A50, D1 thru D50) and somehow unioning them altogether?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 12/21/2012 :  13:41:30  Show Profile  Visit robvolk's Homepage
If you're just generating permutations, you don't really need tables:
with matches(match) as (select 1 union all select match+1 from matches where match<50)
,wins(win) as (select 'H' union all select 'A' union all select 'D')
select match, win from matches cross join wins
If you're actually storing matches between teams and such, that's a different structure.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  13:57:39  Show Profile
Thank you for your response but I probably didn't explain myself clearly.

Your answer provides 150 rows, the solution is probably many hundreds of millions of rows.

50 matches, every single permutation:

1 = HHHHH......(upto 50, all Home Wins)
2 = HHHHH......(upto 50, 49 are H, the last one will be A)

and so on........

Edited by - Rasta Pickles on 12/21/2012 14:58:59
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 12/21/2012 :  16:28:01  Show Profile  Visit robvolk's Homepage
Oh yeah, that's a slight bit bigger:
SELECT POWER(3,50) -- overflows
SELECT POWER(cast(3 as decimal(38)),25) * POWER(cast(3 as decimal(38)),25)  --equivalent
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/21/2012 :  16:30:09  Show Profile
quote:
Originally posted by Rasta Pickles

I know it's do-able in SQL, just don't know how to do it.

50 matches.

Each one can end in a home win (H), a draw (D) or an away win (A).

At it's simplest, all 50 matches end in a home win so I get 50 columns of H.

49 home wins and a draw means I get 49 columns of H and 1 of D.

What code should I use?

TIA.



That would be 3 to the 50th power possible results, somthing like:
717,897,987,691,852,580,000,000

At 4 bytes/row, that would require about 2,611,697,664,877 TB of storage, and that's a bit more than SQL Servers limit of 524,272 TB/Database.


select
	[RowCount] =
	convert(decimal(35,0),power(3.0E,50.0E)),
	[TB at 4 bytes per Row] = 
	convert(decimal(35,0),(power(3.0E,50.0E)*4.0E)/power(1024.0E,4.0E))



Good Luck!



CODO ERGO SUM

Edited by - Michael Valentine Jones on 12/21/2012 16:40:28
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  16:45:10  Show Profile
Thanks for that.

Sooooo, after 2000 years here, us humans are still pretty much pond life in the grand scheme of things.

And people still believe that 43 years ago we went to the moon.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 12/21/2012 :  16:47:56  Show Profile  Visit robvolk's Homepage
Yeah, but if you use 7-zip on maximum settings, you can probably get that down to less than 4,611,697,664 TB. In about 100,000 years.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  17:09:35  Show Profile
And if you'd mentioned the words "zip file" to Neil Armstrong (God bless him for keeping quiet until his death) he'd have given you a blank stare.

One day the human race will wake up
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/22/2012 :  01:44:53  Show Profile
quote:
Originally posted by Rasta Pickles

Thanks for that.

Sooooo, after 2000 years here, us humans are still pretty much pond life in the grand scheme of things.

And people still believe that 43 years ago we went to the moon.





2000 years? Anatomically modern humans evolved from archaic Homo sapiens in the Middle Paleolithic, about 200,000 years ago, according to this article:
http://en.wikipedia.org/wiki/Anatomically_modern_humans

Are you suggesting that they didn't go to the moon in 1969?









CODO ERGO SUM
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/22/2012 :  14:33:26  Show Profile
Not a subject for this forum but it isn't rocket science (see what I did there?) to work out whether the technology was available 43 years ago.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000