GENERATE COMBINATIONS - SQL Server Forums

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

GENERATE COMBINATIONS

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 04:09:58

Hi

I need to generate the combination of numbers within the boundary values.

Example
Boundary Values
Lower value = 94
Upper Value = 99
combination is 8

The above values are dynamic user inputs..

I want to generate the unique combinations order like this...

94 95 96 97 98 99 95 96
94 96 95 98 97 94 96 97 --This combination 8 values user input
94 95 96 97 98 99 94 95

Please help on this...

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 04:21:36

what is the rule of your combination ? Any limit on a number repeating in the series ?

basically you can use CROSS JOIN to do it.

KH
[spoiler]Time is always against us[/spoiler]

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 04:37:27

Hi

Cross join can't use right there no data to put join...

Combination will randomly generate within the limit 94 to 99 with 8 combination

example


1  2  3  4  5  6  7  8
94 96 95 98 97 94 96 97 
94 95 96 97 98 99 94 95

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 04:40:25

[code]
SELECT distinct n1.NUMBER, n2.NUMBER, n3.NUMBER, n4.NUMBER, n5.NUMBER, n6.NUMBER, n7.NUMBER, n8.NUMBER
FROM F_TABLE_NUMBER_RANGE(94, 99) n1
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n2
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n3
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n4
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n5
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n6
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n7
CROSS JOIN F_TABLE_NUMBER_RANGE(94, 99) n8
[/code]

KH
[spoiler]Time is always against us[/spoiler]

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 04:42:22

Hi KH

thanks

But limit values and comination value are user input

How i can generate 94 to 99 your OP is based on table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 05:03:19

Are you using SQL Server 2000, 2005 or 2008?

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 05:10:17

Hi peso

SQL SERVER 2005

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 06:11:17

Hi peso

Are you there ....:-)
we are using SQL SERVER 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 06:33:25

And the results 1..8 are columns?
Or the result is a string containing space to delimit data?

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 06:36:39

Hi

Yes its columns or if delimit also fine..but again we have to split

Combinations are 8 its user input

For Example

1 2 3 4 5 6 7 8
94 96 95 98 97 94 96 97
94 95 96 97 98 99 94 95

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 07:01:42

Do you want all possible combinations?
In this case 1,679,616 records...

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 07:03:49

Hi
Yes i want all combinations (1,679,616 records)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 07:41:06

[code]-- Prepare user supplied parameters
DECLARE @LowerLimit INT,
@UpperLimit INT,
@Combinations INT

SELECT @LowerLimit = 94,
@UpperLimit = 99,
@Combinations = 8

-- Initialize control variables
DECLARE @Data VARCHAR(MAX),
@SELECT VARCHAR(MAX),
@FROM VARCHAR(MAX)

SELECT @SELECT = '',
@FROM = '',
@Data = ''

WHILE @LowerLimit <= @UpperLimit
SELECT @Data = @Data + 'UNION ALL SELECT ' + CAST(@LowerLimit AS VARCHAR(12)) + ' AS i ',
@LowerLimit = @LowerLimit + 1

SET @Data = RTRIM(STUFF(@Data, 1, 10, '(')) + ')'

WHILE @Combinations > 0
SELECT @SELECT = @SELECT + ',d' + CAST(@Combinations AS VARCHAR(12)) + '.i',
@FROM = @FROM + ' CROSS JOIN ' + @Data + ' AS d' + CAST(@Combinations AS VARCHAR(12)),
@Combinations = @Combinations - 1

SELECT @SELECT = 'SELECT ' + STUFF(@SELECT, 1, 1, ' '),
@FROM = STUFF(@FROM, 1, 11, ' FROM ')

EXEC (@SELECT + @FROM)[/code]

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 07:54:03

i wonder how long does it takes to generate 1,679,616 records

KH
[spoiler]Time is always against us[/spoiler]

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 08:15:01

Hi Peso

Thanks a Lot

Can u pls tell me what is the maximum of combinations we can give i mean(@Combinations = X)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 10:26:59

quote:
Originally posted by khtan

i wonder how long does it takes to generate 1,679,616 records

14 seconds.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 10:28:57

quote:
Originally posted by WoodHouse

Can u pls tell me what is the maximum of combinations we can give i mean(@Combinations = X)

About 1,000 (1k).

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-21 : 10:57:04

Hi Peso

I was given @Combinations = 100 but i got below error

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-21 : 17:21:38

That can be anything. You have to be more specific.
Also, where did the error show up?

Using 94-99 in 100 combinations will return 6.53e+77 records !

N 56°04'39.26"
E 12°55'05.63"

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-22 : 04:53:42

Hi peso

I am using the same value
(Using 94-99 in 100 combinations).but i got the error..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-22 : 05:21:10

Yes.
SQL Server will try to create
653,318,623,500,070,906,096,690,267,158,057,820,537,143,710,472,954,871,543,071,966,369,497,141,477,376 records!

Which is the same as 6^100.

N 56°04'39.26"
E 12°55'05.63"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources