Just for fun Lotto SQL

[scripter]

The data table looks like this

date num1 num2 num3 num4 num5 num6
1/1/06 1 45 32 21 15 48

SQL script 1 should be able to do the following
show the Top 3 number pairs
another words if the pair 45 and 15 shows up 98 times and 32 and 12 shows up 3 times

then I want to have 45 and 15 show as one of the top 3 number pairs

I know this is a difficult one

So that being said see if I can re-explain it here
I have say two dates
1/1/2006 and 1/2/2006
on the first date I have six numbers being
1,24,45,35,47,14
and the second date I have
24,35,9,15,46,17

Now I can if it is needed to re do the data structure to reflect it like this
date num
1/1/2006 1
1/1/2006 24
1/1/2006 45
1/1/2006 35
1/1/2006 47
1/1/2006 14

1/2/2006 24
1/2/2006 35
1/2/2006 9
1/2/2006 15
1/2/2006 46
1/2/2006 17

Now somehow I am not sure how but I need to take the six numbers from each date and compare them with every other date.

so another words from date one take 1 and 24 and see if that combo matches other dates then go to 1 and 45 and do the same and so on.

[MCP]

My approach would be something like the following, assuming the second data structure. (It's untested)

select top 3 d1.num num1, d2.num num2
from data d1 inner join data d2 on (d1.date=d2.date) and (d1.num<d2.num)
group by d1.num, d2.num
having count(distinct d1.date)>1
order by count(*)

[MCP]

actually, no need for the distinct count.. :

select top 3 d1.num num1, d2.num num2
from data d1 inner join data d2 on (d1.date=d2.date) and (d1.num<d2.num)
group by d1.num, d2.num
having count(*)>1
order by count(*)

[MCP]

actually, no need for the distinct count.. :

select top 3 d1.num num1, d2.num num2
from data d1 inner join data d2 on (d1.date=d2.date) and (d1.num<d2.num)
group by d1.num, d2.num
having count(*)>1
order by count(*)

aah.. i keep on missing the "desc" in the order by, but that should be easy enough to figure out :)

select top 3 d1.num num1, d2.num num2
from data d1 inner join data d2 on (d1.date=d2.date) and (d1.num<d2.num)
group by d1.num, d2.num
having count(*)>1
order by count(*) desc