Sort teams of a company distinct

Question

I have a database for generate matches between teams of different company.

Example:

Company A has teams: team1, team2, team3
Company B has teams: comp1, comp2
Company C has team: companyc
Company D has teams: companyd1, companyd2

I have to generate the matches between teams, for example the team1 can't play with the team2 because is the same company.

I have this part, but other requirement is that they can play in consecutive order, example if the first match is the team1 vs comp1 the second match should be between company c vs companyd. How I can order so the same team doesn't have a consecutive match?

This is the query that I try

select Cotejos.id_pelea, Partido1, Nombre1, Partido2, Nombre2,
 ronda = ROW_NUMBER() OVER(partition by Partido1 order by Partido1) 
  from Cotejos 
order by  ronda, newid()

give me the results in order of partido1 but partido 2 is repeated in the same ronda

for example the ordes is somehthing like this

  Partido1    Nombre1              Partido2    Nombre2              ronda
----------- -------------------- ----------- -------------------- ----------
58          JJ Y LA ESTRELLA 2   1           AZTECA LAGUNA 3      1
123         LA JOYA 3            1           AZTECA LAGUNA 3      1
141         EL MILAGRO 2         1           AZTECA LAGUNA 1      1
2           GREGORIO GARCIA      3           CHICHO 1             1
5           GUARDADO 1           11          MA LUISA 2           1
119         GUARDADO 2           11          MA LUISA 3           1
14          RIELEROS Y CUMBRES 2 13          COMPADRES Y 28 DE OC 1

And I like something like this

 Partido1    Nombre1              Partido2    Nombre2          ronda
----------- ----------- -------------------- ----------- -------------------
   58          JJ Y LA ESTRELLA 2   1           AZTECA LAGUNA 3      1
   2           GREGORIO GARCIA      3           CHICHO 1             1
   5           GUARDADO 1           11          MA LUISA 2           1
   123         LA JOYA 3            1           AZTECA LAGUNA 3      2
   14          RIELEROS Y CUMBRES 2 13          COMPADRES Y 28 DE OC 2
   119         GUARDADO 2           11          MA LUISA 3           2
   141         EL MILAGRO 2         1           AZTECA LAGUNA 1      3

One option is to generate row_number() for each of the teams inside the companies, for example using newid() and then join the data with full outer join with the row numbers. You have to somehow still make sure that you first pick the companies that have most teams, so that you'll get matches for all the possible teams that exist. — James Z, Apr 29 '17 at 06:37

score 0 · Answer 1 · edited May 23 '17 at 11:47

Hoefully the following will help. Note I borrowed from an answer by endo64 in SQL Server Random Sort

Setup:

CREATE TABLE Table1
    ([Team] varchar(9), [Company] varchar(1))
;

INSERT INTO Table1
    ([Team], [Company])
VALUES
    ('team1', 'a'),
    ('team2', 'a'),
    ('team3', 'a'),
    ('comp1', 'b'),
    ('comp2', 'b'),
    ('companyc', 'c'),
    ('companyd1', 'd'),
    ('companyd2', 'd')
;

query:

select
*
from (
    select
        t1.company t1company
      , t1.team    t1team
      , t2.company
      , t2.team
      , CAST(CHECKSUM(NEWID(), t1.team + t2.team) & 0x7fffffff AS float) x
      , row_number() over(partition by t1.company, t1.team order by t2.company, t2.team) y
    from table1 t1
    inner join table1 t2 on t1.company <> t2.company
    ) d
order by x,y
;

example result:

+----+-----------+-----------+---------+-----------+------------+---+
|    | t1company |  t1team   | company |   team    |     x      | y |
+----+-----------+-----------+---------+-----------+------------+---+
|  1 | d         | companyd2 | a       | team3     |   31131597 | 3 |
|  2 | d         | companyd1 | b       | comp2     |   68902928 | 5 |
|  3 | a         | team1     | d       | companyd1 |   84530144 | 4 |
|  4 | a         | team3     | b       | comp1     |  107683738 | 1 |
|  5 | b         | comp2     | d       | companyd2 |  129452170 | 6 |
|  6 | d         | companyd1 | a       | team1     |  138089373 | 1 |
|  7 | b         | comp1     | d       | companyd1 |  184674639 | 5 |
|  8 | b         | comp2     | a       | team2     |  245733279 | 2 |
|  9 | b         | comp2     | a       | team3     |  288973989 | 3 |
| 10 | c         | companyc  | d       | companyd2 |  364018545 | 7 |
| 11 | c         | companyc  | a       | team2     |  375979006 | 2 |
| 12 | a         | team1     | c       | companyc  |  462231202 | 3 |
| 13 | a         | team1     | b       | comp1     |  486348018 | 1 |
| 14 | a         | team2     | d       | companyd2 |  496110190 | 5 |
| 15 | b         | comp1     | c       | companyc  |  528186078 | 4 |
| 16 | a         | team3     | d       | companyd2 |  546847917 | 5 |
| 17 | b         | comp2     | c       | companyc  |  622910319 | 4 |
| 18 | d         | companyd1 | a       | team3     |  635260954 | 3 |
| 19 | a         | team2     | d       | companyd1 |  669458404 | 4 |
| 20 | a         | team1     | b       | comp2     |  688780068 | 2 |
| 21 | c         | companyc  | a       | team3     |  833897192 | 3 |
| 22 | a         | team3     | c       | companyc  |  872387088 | 3 |
| 23 | b         | comp1     | a       | team2     |  877053008 | 2 |
| 24 | d         | companyd2 | a       | team1     |  902437371 | 1 |
| 25 | c         | companyc  | b       | comp2     |  922241817 | 5 |
| 26 | a         | team3     | b       | comp2     |  967040931 | 2 |
| 27 | d         | companyd1 | a       | team2     |  999743107 | 2 |
| 28 | d         | companyd2 | c       | companyc  | 1018794780 | 6 |
| 29 | b         | comp2     | d       | companyd1 | 1027985306 | 5 |
| 30 | b         | comp1     | a       | team1     | 1073627799 | 1 |
| 31 | d         | companyd2 | b       | comp2     | 1113395974 | 5 |
| 32 | d         | companyd2 | a       | team2     | 1209387221 | 2 |
| 33 | b         | comp1     | d       | companyd2 | 1254602047 | 6 |
| 34 | d         | companyd2 | b       | comp1     | 1334441382 | 4 |
| 35 | b         | comp1     | a       | team3     | 1371977984 | 3 |
| 36 | c         | companyc  | a       | team1     | 1381544075 | 1 |
| 37 | a         | team1     | d       | companyd2 | 1429828466 | 5 |
| 38 | a         | team3     | d       | companyd1 | 1442493066 | 4 |
| 39 | a         | team2     | b       | comp1     | 1456589262 | 1 |
| 40 | d         | companyd1 | c       | companyc  | 1478490615 | 6 |
| 41 | a         | team2     | b       | comp2     | 1506880312 | 2 |
| 42 | b         | comp2     | a       | team1     | 1719739712 | 1 |
| 43 | d         | companyd1 | b       | comp1     | 1881729625 | 4 |
| 44 | c         | companyc  | b       | comp1     | 1979525600 | 4 |
| 45 | c         | companyc  | d       | companyd1 | 2013576803 | 6 |
| 46 | a         | team2     | c       | companyc  | 2140739362 | 3 |
+----+-----------+-----------+---------+-----------+------------+---+

I need that the t1 company and the t2 company doesn't repeat in consecutive mathces, in your sample result the 2 first rows is the same company I need that the company in both side doesn't is consecutive I get that the company doesn't be consecutive only in one side, if I sort by y column the company a is consecutive — abigail armijo, May 22 '17 at 13:52

Sort teams of a company distinct

1 Answers1