| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
 Posted - 12/20/2012 : 14:46:02
|
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
48100 Posts |
Posted - 12/20/2012 : 14:49:34
|
how is data stored in table? or is question on table design?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Mike Jackson
Starting Member
37 Posts |
Posted - 12/21/2012 : 09:55:35
|
Typically we would discourage that many columns and encourage a vertical design, adding rows for win or loss.
Mike
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 12/21/2012 : 10:37:40
|
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). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48100 Posts |
Posted - 12/21/2012 : 11:35:22
|
the solution depends on how data is stored
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 12/21/2012 : 13:21:32
|
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? |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 12/21/2012 : 13:41:30
|
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. |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 12/21/2012 : 13:57:39
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 12/21/2012 : 16:28:01
|
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  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 12/21/2012 : 16:30:09
|
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 |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 12/21/2012 : 16:45:10
|
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.
 |
|
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 12/21/2012 : 16:47:56
|
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.  |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 12/21/2012 : 17:09:35
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 12/22/2012 : 01:44:53
|
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 |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
150 Posts |
Posted - 12/22/2012 : 14:33:26
|
| 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. |
|
|
| |
Topic |
|
All permutations of 50 rugby matches?
Topic Locked
