Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
create table #Personel
Ad varchar(100)

create table #Masa
MasaAdi varchar(100)

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')

select Ad,MasaAdi from
select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
from (
select newid() as ID,Ad,MasaAdi 
from #Personel
cross join #Masa
) t2 where Sira = 1 order by Ad

it's creating the result as shown below.

deneme1 - masa2
deneme2 - masa3
deneme1 - masa1 

well,it's to be able to deploy to different tables two times the same personal.

e.g deneme1 - masa2
    deneme1 - masa1

I don't want to do that.I want to do something as shown below.

e.g deneme1-masa2

So,I don't want to deploy to different tables two times the same personal.

share|improve this question
Why not deneme1-masa1, deneme2-masa2, deneme3-masa3. Please describe the expected output in a clear way. What if Personal and Masa contain different number of rows? – bernd_k Jun 25 '11 at 7:45
@bernd_k - As you mentioned,maybe.I just gave an sample output. – Cell-o Jun 25 '11 at 7:48
you want some unique random assignment? – bernd_k Jun 25 '11 at 7:50
bernd_k - yes, as you said – Cell-o Jun 25 '11 at 7:51
What do you want to do in cases where the number of rows in the two tables is not the same? – Aaron Bertrand Jun 26 '11 at 23:10
up vote 3 down vote accepted
cte1 as
    select *, row_number() over(order by Ad) RowNumber
    from #Personel
cte2 as
    select *, row_number() over(order by newid()) RowNumber
    from #Masa
select cte1.Ad, cte2.MasaAdi
from cte1
    join cte2 on
        cte1.RowNumber = cte2.RowNumber
share|improve this answer
@Cell-o - did the query work? – Alex Aza Jun 25 '11 at 20:26
Do you really need both CTEs to be seeded with random ordering? I suspect only one is needed, and the other could just be ordered by a static column (this would be best if the column was clustered or an NC lead column). The extra sort may not be costly in small/trivial cases but I bet it would be easy to come up with cases where the impact is significant. – Aaron Bertrand Jun 26 '11 at 23:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.