Sql Procedure/View /Query Required

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

SQL Server 2005 Forums

Transact-SQL (2005)

Sql Procedure/View /Query Required

Author

Topic

hksharmaa
Starting Member

16 Posts

Posted - 2011-03-07 : 12:33:53

Hi All...

I want to create all unique combination from a range of 1-100 records of 4 groups each...But i don't want to make them hardcore..1-100 records can vary to 1-1000 etc and also group of 4 may vary to 5,6,7,etc...I want to pass records range and group at run time and system should generate all combination group in a table...

For Eg.. Consisting of records

Table A
1
2
3
4
5
6
7
8
9
.
.
.
100

Table B
Col. 1 Col.2
1 1
2 1
3 1
4 1

5 2
2 2
3 2
4 2

6 3
7 3
8 3
9 3
. .
. .
. .
. .

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-07 : 13:15:34

This is my favorite way to do it. There are whole lot of other methods people use, some of which may be more efficient.

declare @RecordsRange int = 100;
declare @GroupSize int = 5;

with a as 
(
   select top (cast(sqrt(@RecordsRange)+1 as int)) number as N  
   from master..spt_values
)
select top (@RecordsRange) 
   row_number() over (order by a.N) as Col1,
   1 + (row_number() over (order by a.N)-1)/ @GroupSize as Col2
from A cross join A b 
order by Col1

I think I got the arithmetic for the group code right, but not tested it - it may need tweaking.

hksharmaa
Starting Member

16 Posts

Posted - 2011-03-08 : 00:37:52

I am getting errors when running this query in SQL 2005...

hksharmaa
Starting Member

16 Posts

Posted - 2011-03-08 : 01:08:19

It worked in SQL 2008..but it doesn't show all the combinations...It shows only 20 combinations of 100 records in a group of 5...There are more possible combination that aren't generated in this query

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-08 : 06:50:55

hk, I misunderstood your question. You are looking for nCr. I thought you were looking for a sequence partitioned into groups.

It is easy to generate nCr for small values of r if r is known in advance - you just have to join the table of data r times and pick the appropriate join conditions. But, I can't think of a simple way to generate nCr when r is a variable. The only thought that comes to mind is dynamic sql, which is not really scalable for large values of r anyway, and is ugly.

I am not able to think straight at the moment, being the nervous wreck that I am trying to get ready for a job interview :--). There may be a simple way - can you start a new thread and ask for nCr? People may not look at this thread because they think someone already responded to it and has been resolved.

Also, I hope you realize that for the example you gave - 100C4, there will be a very large number of combinations. I think the number of combinations is 100!/[100-4]! That is nearly 1,000,000 groups of 4. That formula may not be right, and I might be off by a million or two, but it is a very large number.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-08 : 06:52:54

The error you saw in SQL 2005 is because of the declaration and assignment. SQL2008 allows it, 2005 does not. Use

declare @RecordsRange int; set @RecordsRange  = 100;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources