Just for fun Lotto SQL
-
-
So you're already past 25 year old too Sergiy?
-
And I'll be long dead. But your rants will be on this site for posterity
Far away is close at hand in the images of elsewhere.
Anon. -
Rants!, What rants?
-
Scott,
Just making sure you got the answer for this same problem that you posted on the Wrox forum.
This is more than ten times faster than what they posted there. I also posted this same thing there.
SELECT TOP 3 d.Num1,d.Num2,COUNT(*) TheCount
FROM
(--Create ALL "pairs" for each date
SELECT t1.Num AS Num1,t2.Num AS Num2
FROM #Test t1,
#Test t2
WHERE t1.Dt = t2.Dt
AND t1.Num < t2.Num
) d
GROUP BY d.Num1,d.Num2
ORDER BY TheCount DESC,Num1,Num2
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Did you post int he right forum. I have no idea what you are talking about.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
But, Scott does
Scott had posted the very same question on the WROX forum (totally different URL)... there, they got a little carried away with discussions on Pascal's triangle and how it related to the solution of this seemingly nasty little problem and a whole bunch of other stuff (kinda like folks did here
). Scott has a realtively large number of lotto results to process and, since each 6 digit lotto number has 15 distinct pairs (that's where the talk of Pascal's triangle started), a thousand lotto numbers will actually spawn 15 thousand rows and a solution could (and did) start to get slow (1.3 seconds instead of ~ 100 milliseconds on 10K lotto numbers).Just in case he gave up on the other forum, I wanted to make sure he got the answer so I posted it here, as well.
By the way, it's also fairly easy to warp my solution to find the most frequent triplets and quadrupelets, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good thing I read this thread. Now I know whom to consult when I pick my lotto numbers.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply