How to script this in SQL server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

How to script this in SQL server?

Author

Topic

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 09:03:25

Basicly i need to generate unique combinations from allot of records.

Each record contains 4 columns which constrains the next selection.

In delphi my program is this way:


 for i := 1 to 49 do
 begin

   with ADOData do
    begin

        CommandText:='SELECT top 1 * FROM PiecesClustered Where P1 not in (' + spieces + ') and P2 not in (' + spieces + ')   and P3 not in (' + spieces + ')  and P4 not in (' + spieces + ')';

        Open;

        if Eof = false then
        begin
          spieces:= spieces + ',' + inttostr(FieldValues['P1']) + ',' + inttostr(FieldValues['P2']) + ',' + inttostr(FieldValues['P3']) + ',' + inttostr(FieldValues['P4']);

          sdata:=sdata + inttostr(FieldValues['PId'])  + ',';
        end;

        Close;
   end;
 end;

Basicly it tries to select 49 rows from the database in which P1 , p2 , p3 , p4 are all unique.

How could i generate a sql query / script which would do this?
Don't mind if it needs huge memory or time to run.

The table maybe is bad designed for this request. But i can change it to whats needed ...

Pid | PData (, sepp p1..p4) |P1|P2|P3|P4

looking forward to suggestions...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 09:18:53

Not sure what you want.
What is the source table
PiecesClustered (P1,P2,P3,P4)?

And you want a reult with 49 rows of 4 columns where no value in any column appears anywhere else in the resultset?

Maybe a couple of rows of example data would help

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 09:53:44

Offcourse:

I need 49 Pid numbers which then all have unique Pdata / P1,p2,p3,p4..

so the four showed are never together in a set of 49 because of atleast p1 is the same...

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 12:43:20

If possible i then want to generate each unique set.
The order of the result doesn't matter. so 123 and 321 = same solution.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 12:55:32

say you had 5 distinct numbers - would that give 5 rows
1,2,3,4
1,2,3,5
1,2.4,5
1,3,4,5
2,3,4,5

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 13:18:09

Yeah exactly the solutions could be what you posted.
Except that there would be 49 id's.

Hope you can help, it looks quite complicated to me..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 13:52:19

first is to get a distinct set of values
select val = p1 from tbl
union
select p2 from tbl
union
select p3 from tbl
union
select p4 from tbl

we can restrict this to a number of values - shuldn't take maany to get 49 rows.

then from that we need distinct results.
If we make sure the values are ascending then a distinct will give the result
so

;with cte as
(
select top 10 * from
(
select val = p1 from tbl
union
select p2 from tbl
union
select p3 from tbl
union
select p4 from tbl
) a
)
select top 49 * from
(
select distinct p1=a.val, p2=b.val, p3=c.val, p4=d.val
from
(select val from cte) a
join (select val from cte) b
on a.val < b.val
join (select val from cte) c
on b.val < c.val
join (select val from cte) d
on c.val < d.val
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 13:54:22

I'm trying something using sql script and if i untherstand correctly i'm gonna use cursors.... :(



Declare @intFlag INT
Declare @i INT
Declare @ifound INT

Declare @sdata varchar(1500)
Declare @pdata varchar(1500)

Set @intFlag  = 1

set @sdata=''
set @pdata=''

While (@intFlag < 1000)
begin
 
  print @intflag
 
   Select top 1 Pid,P1,P2,P3,P4 from PiecesClustered where PId=@intFlag
   
   Set @sdata = Pid
   Set @pdata = P1 + ',' + P2 + ',' + P3 + ',' + P4
   
   While (@i < 49)
   begin
           
			Select top 1 Pid,P1,p2,p3,p4 from PiecesClustered where PId=@intFlag
   			
   			if pid is not null then
   			begin
   				Set @sdata = @sdata + ',' + Pid
				Set @pdata = @pdata + ',' + P1 + ',' + P2 + ',' + P3 + ',' + P4
				set @ifound = @ifound +1
            end
                   
		Set @i = @i + 1
   end
  
    if @ifound = 49 then
    begin
     insert into Sols values(@sdata)
     set @sdata=''
    end 
  
 Set @intFlag=@intFlag+1
 
 end
 go

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 14:01:37

Okey,

I'm trying to learn what you do.
And i have got some extra information.
the problem is i dunno what you need to know...

the p1 until p4 numbers are from 61 to 256 so 196 numbers.
But there are allot of combinations with those 196 numbers differentiated over de p1..p4

How do i run your code?
what means CTE?
i cant see nowhere the table name used?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 14:10:51

I'm assuming you have a table named tbl with 4 values per row p1-p4
The code I gave will take those values and produce a set of distinct values.
Then it produces 49 rows each with a different combination of 4 of those values.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 14:12:51

Sorry,

I found tbl and filled in the name of the table.
And the query ran.

But i cant get the result needed.

I get 49 rows back, but the p1..p4 values aren't distinct from each other.

i did delete the top 10 because its obvious one needs more data to find a solution. right?

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 14:19:49

Mhh

maybe i'm not clear enough.

the 49rows need to have per row a unique p1..p4

so in total i get 4 values (p1..p4) per row * 49 = 196.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 14:29:40

You mean it's unique values rather than unique combinations?
so 5 values would give one possible row - could be one of 5 rows but the would only result in a single row?
So you just need to choose the 196 distinct values and allocate 4 to each row to get the 49 rows.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 14:36:13

;with cte as
(
select top 196 * from
(
select val = p1 from tbl
union
select p2 from tbl
union
select p3 from tbl
union
select p4 from tbl
) a
)
(select val, seq = row_number() over (order by val) from cte) a
join (select val, seq = row_number() over (order by val) from cte) b
on a.seq = b.seq-1
and a.seq%4=1
join (select val, seq = row_number() over (order by val) from cte) c
on b.seq = c.seq-1
join (select val, seq = row_number() over (order by val) from cte) d
on c.seq = d.seq-1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 14:55:02

cant get the second half part to work...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 15:06:22

sorry

declare @tbl table (p1 int, p2 int, p3 int, p4 int)
insert @tbl select 1,2,3,4
insert @tbl select 2,3,4,5
insert @tbl select 8,5,7,9
insert @tbl select 10,5,11,6
insert @tbl select 12,5,11,6

;with cte as
(
select top 196 * from
(
select val = p1 from @tbl
union
select p2 from @tbl
union
select p3 from @tbl
union
select p4 from @tbl
) a
)
select p1=a.val, p2=b.val, p3=c.val, p4=d.val from
(select val, seq = row_number() over (order by val) from cte) a
join (select val, seq = row_number() over (order by val) from cte) b
on a.seq = b.seq-1
and a.seq%4=1
join (select val, seq = row_number() over (order by val) from cte) c
on b.seq = c.seq-1
join (select val, seq = row_number() over (order by val) from cte) d
on c.seq = d.seq-1

result
p1 p2 p3 p4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 15:14:01

Bloody nice!!

Now we only need one last thing.

maybe its not small.

Now i need to know the 49 row id's...
(see picture above)

and then i need to have all the combinations possible in the database...

Whish i could do this!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 15:36:57

That will give all the possible combinations.
Note that even with 12 different value you get 495 combinations with 13 it is 715
It will increase quickly.

declare @tbl table (p1 int, p2 int, p3 int, p4 int)
insert @tbl select 1,2,3,4
insert @tbl select 2,3,4,5
insert @tbl select 8,5,7,9
insert @tbl select 10,5,11,6
insert @tbl select 12,5,11,6

;with cte as
(
select * from
(
select val = p1 from @tbl
union
select p2 from @tbl
union
select p3 from @tbl
union
select p4 from @tbl
) a
)
select distinct p1=a.val, p2=b.val, p3=c.val, p4=d.val, pid = row_number() over (order by a.val,b.val,c.val,d.val)
from
(select val from cte) a
join (select val from cte) b
on a.val < b.val
join (select val from cte) c
on b.val < c.val
join (select val from cte) d
on c.val < d.val

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-20 : 17:20:32

almost there...

in your first approach you are spot on.
because of the fact that one can only get 49 rows which are different.
49 x 4 = 196. there are only 196 different numbers in p1..p4.

But then every row in the original db has a row id (see picture called PID).

And that id is needed for me.
so in fact one solution consist of 49 row id's which consist of 196 different numbers which are in p1..p4

hope you are willing to help...
what a brick this is...

How can i run your code x times to get all the different solutions?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 17:31:01

say you have
pid = 1 1, 2, 3, 4
pid = 2 2, 3, 4, 5
pid = 3 1, 2, 3, 6

in the result you have a row 1, 2, 5, 6
which pid(s) do you want to allocate to that?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tks
Starting Member

48 Posts

Posted - 2010-11-21 : 06:21:37

Ow sorry Nigel,

There is the problem of untherstanding...

If the data is like this...

pid = 1 1, 2, 3, 4
pid = 2 2, 3, 4, 5
pid = 3 1, 2, 3, 6

There cant be an solution. Because 3 is in every Pid...

If you use this as data:

declare @tbl table (PId int,p1 int, p2 int, p3 int, p4 int)
insert @tbl select 0,1,2,3,4
insert @tbl select 1,2,3,4,5
insert @tbl select 2,8,5,7,9
insert @tbl select 3,10,5,11,6
insert @tbl select 4,12,5,11,6

as we would assume that instead of 49 we only need 2 rows for an solution.

it needs to show up (maybe more)
Nr PIDs (comma sepparated)
1 0,2
2 0,3
3 0,4

if i would(or could) down drill on 1:
then there was:
Nr P1,p2,p3,p4
1 1,2,3,4
2 8,5,7,9

So basicly that is the complete problem.
Your first idea works nice. because it shows up the downdrilled result. To generate one solution it takes 20sec. which is an awsome performance!

So basicly the first step you did was good.
But now we need to group by those 49 results and create out of that an , sepp data string.

And then we need all the combinations possible.
But remember:

Nr PID's
1 0,1
2 1,0

This is the same for me.

Complicated?

You are on the right way!

Will assist you where i can...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources