I was watching this video yesterday on how unlikely it is to shuffle a deck of cards the same way twice:
https://youtu.be/czSdEhtXwm4?t=300
And I thought to myself "My SAN adminsitrator still has some hair left."
So I built a quick deck of cards:
declare @card int
create table deck (card int)
set @card = 1
while @card <= 52
begin
insert into deck (card)
values (@card)
set @card += 1
end
Shuffled them up:
declare @shuffleID bigint
create table shuffles (shuffleID bigint, position int, card int)
select @shuffleID = isnull(max(shuffleid),0)+ 1 from shuffles
while @shuffleID <= 10000
begin
insert into shuffles (shuffleID, position, card)
select @shuffleID, rank() over (order by newid()), card from deck
order by newid()
set @shuffleID += 1
end
Then saw how closely I could match them:
select s1.shuffleID, s2.shuffleID, count(1) as match_count from shuffles s1
inner join shuffles s2 on s1.position = s2.position and s1.card = s2.card
and s1.shuffleID != s2.shuffleID
group by s1.shuffleID, s2.shuffleID
order by count(1) desc
The whole script looks like this:
if object_id('dbo.deck') is not null begin drop table deck end
if object_id('dbo.shuffles') is not null begin drop table shuffles end
create table deck (card int)
create table shuffles (shuffleID bigint, position int, card int)
CREATE NONCLUSTERED INDEX [huehuehue]
ON [dbo].[shuffles] ([position],[card])
INCLUDE ([shuffleID])
declare @card int,
@shuffleID bigint,
@sql int
set @card = 1
while @card <= 52
begin
insert into deck (card)
values (@card)
set @card += 1
end
select @shuffleID = isnull(max(shuffleid),0)+ 1 from shuffles
while @shuffleID <= 1000
begin
insert into shuffles (shuffleID, position, card)
select @shuffleID, rank() over (order by newid()), card from deck
order by newid()
set @shuffleID += 1
end
select s1.shuffleID, s2.shuffleID, count(1) as match_count from shuffles s1
inner join shuffles s2 on s1.position = s2.position and s1.card = s2.card
and s1.shuffleID != s2.shuffleID
group by s1.shuffleID, s2.shuffleID
order by count(1) desc
The highest match I've got out of 1,000 shuffles is 8.
Question: What would be the fastest way to shuffle a deck of cards the same way twice?
Best Answer
Generally, inner join on a key
A big number as described in a few places
1 in 806,581,751,709,438,785,716,606,368,564,037,669,752,895,054,408,832,778,240,000,000,000
One way would be to just use a single table. I use a tally table for the initial insert to speed that up, and also use two CTE's for the shuffle. Is it the fastest? Not sure, but it's pretty quick!
Takes about 4 milliseconds on my machine for the initial and follow on shuffles, on average.
STATISTICS TIME FOR ENTIRE SCRIPT