need help with column formatting and writing query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help with column formatting and writing query

Author

Topic

BZboy
Starting Member

9 Posts

Posted - 2008-12-10 : 13:31:03

Hello all. There are a few quertions that I have. I need to create a table that has 2 columns: number_set, date.
The number_set column will have 5 numbers from each day ranging from 1-35.
For example:

Number_Table:
Number_set Date
1 2 3 4 5 12/01/2008

What is the best way to format the number column? there will be 5 numbers in each cell for each day. Is it best to add the set of number with commas? or spaces?

Also what I will need to do after is write a query that pulls the most least common number combination out of the past 365 days. or a query to pull a random # combination

any help would be appreciated =)

thank you

this is somehting that cld be used in MS Access or sql server

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-10 : 13:51:46

It doesn't matter how you separate the numbers as long as there's a unambitious delimiter you should be fine.

Are the number_sets always in order?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-10 : 14:03:00

Most common number_set:


select top 1 Number_set from number_table group by number_set order by count(number_set) desc

Random pull a number set from your table:


select top 1 number_set from Number_table order by newid()

Random Combination: (random generate 5 numbers 1-35)


select cast(number as varchar(2))+' ' from 
(select top 5 number
from master..spt_values 
where type = 'p' and 
number between 1 and 35 
order by newid())t
order by number for xml path('')

BZboy
Starting Member

9 Posts

Posted - 2008-12-10 : 14:14:57

Thanks for the quick reply!. Basically you could compare it to a lottery. Here is an example of what im trying to do

Say I have the following table for a weeks worth of winning lotto numbers. (the drawing would be daily). Say you pick 5 numbers ranging from 1-35.

here is what the table would look like:

Number_tble1
Number_selection Date
1 2 3 4 5 12/01/2008
6 7 8 9 10 12/02/2008
11 12 14 34 35 12/03/2008
15 17 23 24 25 12/04/2008
3 4 7 8 9 12/05/2008
7 9 11 15 28 12/06/2008
15 22 27 30 31 12/07/2008

If the set of 5 numbers could be added each day into a table like above the one above, then im fine with that.

The main question is, if I can update the table daily and put in the set of 5 numbers for that day, I need a query that will forecast the most likely set of numbers to be pulled on the following day based on the previous sets of numbers that have been pulled in the past. or If I wanted to pull a random set of 5 numbers on a daily basis from the above table

BZboy
Starting Member

9 Posts

Posted - 2008-12-10 : 14:36:01

One more thing, I guess I should have clarified this before. I need the query to pull the most likely 5 next #'s without selecting the exact 3's thast have been pulled in the past. Some of them can be the same- for ex say the last set added was 1-2-3-4-5 then the new set could be 2-4-5-7-8....any ideas how to do this?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-10 : 14:58:35

The previous entries is not relevant to the next 5 numbers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources