14 Replies Latest reply on Jul 2, 2013 9:18 PM by BrendanP

# Processing Cost - How to catch a soccer team with the highest combined score?

Hi to all.

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta and I am working with the two follows tables.

I want to get a soccer team with the highest combined score averages and participations considering all possible formations and my budget, but I'm not finding a way to get a cost effective processing.

Suggestions?

```create table ESQUEMA
(
ESQUEMA   INTEGER,
ZAGUEIROS INTEGER,
LATERAIS  INTEGER,
MEIAS     INTEGER,
ATACANTES INTEGER,
P1        CHAR(3),
P2        CHAR(3),
P3        CHAR(3),
P4        CHAR(3),
P5        CHAR(3),
P6        CHAR(3),
P7        CHAR(3),
P8        CHAR(3),
P9        CHAR(3),
P10       CHAR(3)
)
;

```

esquema table - sample data

 ESQUEMA ZAGUEIROS LATERAIS MEIAS ATACANTES P1 P2 P3 P4 P5 P6 P7 P8 P9 P10 1 442 2 2 4 2 ZAG ZAG LAT LAT MEI MEI MEI MEI ATA ATA 2 433 2 2 3 3 ZAG ZAG LAT LAT MEI MEI MEI ATA ATA ATA 3 532 3 2 3 2 ZAG ZAG ZAG LAT LAT MEI MEI MEI ATA ATA 4 352 3 5 2 ZAG ZAG ZAG MEI MEI MEI MEI MEI ATA ATA 5 343 3 4 3 ZAG ZAG ZAG MEI MEI MEI MEI ATA ATA ATA 6 451 2 2 5 1 ZAG ZAG LAT LAT MEI MEI MEI MEI MEI ATA 7 541 3 2 4 1 ZAG ZAG ZAG LAT LAT MEI MEI MEI MEI ATA

```create table JOGADORES
(
ID      INTEGER not null,
CLUBE   VARCHAR2(100),
NOME    VARCHAR2(100),
POSICAO CHAR(3),
PRECO   NUMBER(4,2),
MEDIA   NUMBER(4,2),
JOGOS   INTEGER
)
;

```

 ID CLUBE NOME POSICAO PRECO MEDIA JOGOS 80 403 Fluminense Abel Braga TEC 17,51 5,36 4 70 393 Coritiba Alex MEI 16,98 5,08 5 42 365 Náutico Auremir LAT 7,73 5,48 4 18 341 Grêmio Barcos ATA 18,96 3,67 4 102 425 Grêmio Bressan ZAG 10,85 5,90 4 31 354 Crisciúma Bruno GOL 10,66 3,20 5 105 428 Cruzeiro Bruno Rodrigo ZAG 15,47 5,28 5 82 405 Vitória Caio Júnior TEC 11,40 4,45 5 41 364 Fluminense Carlinhos LAT 12,40 6,93 3 61 384 Vasco Carlos Alberto MEI 15,01 6,75 2 13 336 Ponte Preta Chiquinho ATA 9,97 4,64 5 72 395 Ponte Preta Cicinho MEI 11,42 4,72 5 92 415 Santos Claudinei Oliveira TEC 11,92 3,17 3 104 427 Ponte Preta Cléber ZAG 14,61 5,78 5 68 391 Portuguesa Correa MEI 8,44 5,60 4 93 416 Bahia Cristóvão Borges TEC 8,27 2,92 5 96 419 Atlético-MG Cuca TEC 12,62 2,32 4 29 352 Corinthians Cássio GOL 12,51 3,74 5

I want a result like this query could bring, if it had a low cost. Should I make any changes in tables or attempting to use PL / SQL?

```select *
esquema   e
where j1.posicao = e.p1
and j2.posicao = e.p2
and j3.posicao = e.p3
and j4.posicao = e.p4
and j5.posicao = e.p5
and j6.posicao = e.p6
and j7.posicao = e.p7
and j8.posicao = e.p8
and j9.posicao = e.p9
and j10.posicao = e.p10
and j11.posicao = 'TEC'
and j12.posicao = 'GOL'
--
and j1.id NOT IN (j2.id,
j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j2.id NOT IN (j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j3.id NOT IN
(j4.id, j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j4.id NOT IN
(j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j5.id NOT IN (j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)
--
and j9.id NOT IN (j10.id, j11.id, j12.id)
--
and j10.id NOT IN (j11.id, j12.id)
--
and j11.id <> j12.id
and (e.esquema,
(j1.media + j2.media + j3.media + j4.media + j5.media + j6.media +
j7.media + j8.media + j9.media + j10.media + j11.media + j12.media +
j1.jogos + j2.jogos + j3.jogos + j4.jogos + j5.jogos + j6.jogos +
j7.jogos + j8.jogos + j9.jogos + j10.jogos + j11.jogos + j12.jogos)) in
(select e.esquema,
nvl(max(j1.media + j2.media + j3.media + j4.media + j5.media +
j6.media + j7.media + j8.media + j9.media + j10.media +
j11.media + j12.media + j1.jogos + j2.jogos +
j3.jogos + j4.jogos + j5.jogos + j6.jogos + j7.jogos +
j8.jogos + j9.jogos + j10.jogos + j11.jogos +
j12.jogos),
0)
esquema   e
where j1.posicao = e.p1
and j2.posicao = e.p2
and j3.posicao = e.p3
and j4.posicao = e.p4
and j5.posicao = e.p5
and j6.posicao = e.p6
and j7.posicao = e.p7
and j8.posicao = e.p8
and j9.posicao = e.p9
and j10.posicao = e.p10
and j11.posicao = 'TEC'
and j12.posicao = 'GOL'
--
and j1.id NOT IN (j2.id,
j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j2.id NOT IN (j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j3.id NOT IN (j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j4.id NOT IN
(j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j5.id NOT IN
(j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)
--
and j9.id NOT IN (j10.id, j11.id, j12.id)
--
and j10.id NOT IN (j11.id, j12.id)
--
and j11.id <> j12.id
and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco +
j6.preco + j7.preco + j8.preco + j9.preco + j10.preco +
j11.preco + j12.preco) <= &patrimonio
group by e.esquema)
and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco + j6.preco +
j7.preco + j8.preco + j9.preco + j10.preco + j11.preco + j12.preco) <=
&patrimonio;

```

Filippe

• ###### 1. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Provide a sample required output with explanation and rules, if any (since there may be atleast few people like me who dont know anything about soccer ). That'll help better in formulating a query

• ###### 2. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Message was edited by: BrendanP Forum has truncated this post, so I made another one using '&lt' and '&gt' - see the earlier post

Also, put it on my blog, here

Fantasy Football Team Selection in SQL

Message was edited by: BrendanP

• ###### 3. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Nice question, fantasy football - this is another knapsack problem, single container version. I've solved that on this forum before and here adapt the solution for this case. Thanks to OP for specifying in two languages to make it more interesting:)

Note that I have taken OP's test data but anglicised the columns (no guarantee of language correctness though), and removed unnecessary columns, etc. You need a definite objective function, so I have just multiplied appearances by average points, but you can use any function of the fields. I have also arbitrarily taken a maximum price of 130.

Performance is of course a key issue with combinatorial problems; hopefully, using the positional constraints early on helps with that.

```Input Formations
SQL&gt SELECT *
2    FROM formations
3   ORDER BY 1
4  /

ID  CENTRE_BACKS WING_BACKS MIDFIELDERS   FORWARDS
--- ------------ ---------- ----------- ----------
343            3          0           4          3
352            3          0           5          2
433            2          2           3          3
442            2          2           4          2
451            2          2           5          1
532            3          2           3          2
541            3          2           4          1

7 rows selected.

SQL&gt COLUMN club_name FORMAT A15
SQL&gt COLUMN player_name FORMAT A20
SQL&gt PROMPT Input Players
Input Players
SQL&gt SELECT id,
2         club_name,
3         player_name,
4         position,
5         price,
6         avg_points,
7         appearances,
8         avg_points * appearances tot_points,
9         Round (avg_points * appearances / price, 2) utility,
10         Rank() OVER (ORDER BY avg_points * appearances DESC) t_rank,
11         Rank() OVER (ORDER BY price) p_rank
12    FROM players
13   ORDER BY avg_points * appearances / price DESC
14  /

ID CLUB_NAME       PLAYER_NAME          PO      PRICE AVG_POINTS APPEARANCES TOT_POINTS    UTILITY     T_RANK     P_RANK
---------- --------------- -------------------- -- ---------- ---------- ----------- ---------- ---------- ---------- ----------
4 Náutico         Auremir              WB       7.73       5.48           4      21.92       2.84          9          1
14 Portuguesa      Correa               MF       8.44        5.6           4       22.4       2.65          7          3
17 Ponte Preta     Chiquinho            FW       9.97       4.64           5       23.2       2.33          6          4
1 Grêmio          Bressan              CB      10.85        5.9           4       23.6       2.18          4          6
12 Ponte Preta     Cicinho              MF      11.42       4.72           5       23.6       2.07          4          8
3 Ponte Preta     Cléber               CB      14.61       5.78           5       28.9       1.98          1         13
10 Vitória         Caio Júnior          MF       11.4       4.45           5      22.25       1.95          8          7
15 Bahia           Cristóvão Borges     MF       8.27       2.92           5       14.6       1.77         15          2
2 Cruzeiro        Bruno Rodrigo        CB      15.47       5.28           5       26.4       1.71          2         15
7 Fluminense      Carlinhos            WB       12.4       6.93           3      20.79       1.68         11         10
5 Crisciúma       Bruno                WB      10.66        3.2           5         16        1.5         13          5
9 Coritiba        Alex                 MF      16.98       5.08           5       25.4        1.5          3         16
6 Corinthians     Cássio               WB      12.51       3.74           5       18.7       1.49         12         11
8 Fluminense      Abel Braga           MF      17.51       5.36           4      21.44       1.22         10         17
11 Vasco           Carlos Alberto       MF      15.01       6.75           2       13.5         .9         16         14
13 Santos          Claudinei Oliveira   MF      11.92       3.17           3       9.51         .8         17          9
18 Grêmio          Barcos               FW      18.96       3.67           4      14.68        .77         14         18
16 Atlético-MG     Cuca                 MF      12.62       2.32           4       9.28        .74         18         12

18 rows selected.

SQL&gt PROMPT Top ten solutions
Top ten solutions
SQL&gt WITH rsf (nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
2  SELECT p.id, 0 lev, p.price, p.avg_points * p.appearances,
3         CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
4         CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
5         CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
6         CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
7         To_Char (p.id) path
8    FROM players p
9   WHERE EXISTS (SELECT 1 FROM formations WHERE
10                     centre_backs &gt= CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
11                     wing_backs   &gt= CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
12                     midfielders  &gt= CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
13                     forwards     &gt= CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
14                )
15   UNION ALL
16  SELECT p.id,
17         r.lev + 1,
18         r.tot_price + p.price,
19         r.tot_profit + p.avg_points * p.appearances,
20         r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
21         r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
22         r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
23         r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
24         r.path || ',' || To_Char (p.id)
25    FROM rsf r
26    JOIN players p
27      ON p.id &gt r.nxt_id
28   WHERE EXISTS (SELECT 1 FROM formations WHERE
29                     centre_backs &gt= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
30                     wing_backs   &gt= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
31                     midfielders  &gt= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
32                     forwards     &gt= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
33                )
34    AND r.tot_price + p.price &lt= 130
35  ) SEARCH DEPTH FIRST BY nxt_id SET line_no
36  , paths_ranked AS (
37  SELECT tot_price,
38         tot_profit,
39         Row_Number () OVER (ORDER BY tot_profit DESC) r_profit,
40         path
41    FROM rsf
42  ), top_ten_paths AS (
43  SELECT tot_price,
44         tot_profit,
45         r_profit,
46         ',' || path || ',' path,
47         player_index
48    FROM paths_ranked
49    CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL &lt 11)
50   WHERE r_profit &lt= 10
51  ), top_ten_teams AS (
52  SELECT tot_price,
53         tot_profit,
54         r_profit,
55         path,
56         player_index,
57         Substr (path,
58                 Instr (path, ',', 1, player_index) + 1,
59                 Instr (path, ',', 1, player_index + 1) - Instr (path, ',', 1, player_index) - 1) player_id
60    FROM top_ten_paths
61  )
62  SELECT t.tot_profit,
63         t.tot_price,
64         t.r_profit rnk,
65         p.position,
66         t.player_id p_id,
67         p.player_name,
68         p.club_name,
69         p.price,
70         p.avg_points,
71         p.appearances apps,
72         p.avg_points * p.appearances profit
73    FROM top_ten_teams t
74    JOIN players p
75      ON p.id = t.player_id
76  ORDER BY t.r_profit, t.path, t.player_index
77  /

TOT_PROFIT  TOT_PRICE        RNK PO P_ID PLAYER_NAME          CLUB_NAME            PRICE AVG_POINTS       APPS     PROFIT
---------- ---------- ---------- -- ---- -------------------- --------------- ---------- ---------- ---------- ----------
238.46     119.27          1 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
237.65     125.38          2 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
9    Alex                 Coritiba             16.98       5.08          5       25.4
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
237.5     128.34          3 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
236.37     119.38          4 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
6    Cássio               Corinthians          12.51       3.74          5       18.7
MF 9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
236.3     125.36          5 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
125.93          6 CB 2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
235.56     125.49          7 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
6    Cássio               Corinthians          12.51       3.74          5       18.7
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
9    Alex                 Coritiba             16.98       5.08          5       25.4
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
235.41     128.45          8 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
6    Cássio               Corinthians          12.51       3.74          5       18.7
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
235.24     124.05          9 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 6    Cássio               Corinthians          12.51       3.74          5       18.7
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 9    Alex                 Coritiba             16.98       5.08          5       25.4
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
234.5      119.8         10 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
3    Cléber               Ponte Preta          14.61       5.78          5       28.9
WB 4    Auremir              Náutico               7.73       5.48          4      21.92
7    Carlinhos            Fluminense            12.4       6.93          3      20.79
MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
10   Caio Júnior          Vitória               11.4       4.45          5      22.25
12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
14   Correa               Portuguesa            8.44        5.6          4       22.4
FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2

100 rows selected.

Elapsed: 00:00:05.55```
• ###### 4. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Hi.

Note: coach (TEC) and goalkeeper (GOL) are standard players.

Seeking twelve players that add the greatest result: average points + number of participations. Therefore, considering all the formations. Which returns me the best result? For example: suppose we get the largest selection of summation and within my budget (I consider 200) with 442 formation. Follow simulation.

The largest sum was found: 134.71. The formation that allowed this sum was: 442.

```select '442' FORMATION,
(j1.media + j1.jogos + j2.media + j2.jogos + j3.media + j3.jogos +
j4.media + j4.jogos + j5.media + j5.jogos + j6.media + j6.jogos +
j7.media + j7.jogos + j8.media + j8.jogos + j9.media + j9.jogos +
j10.media + j10.jogos + j11.media + j11.jogos + j12.media +
j12.jogos) GREATEST_RESULT,
(j1.preco + j2.preco + j3.preco + j4.preco + j5.preco + j6.preco +
j7.preco + j8.preco + j9.preco + j10.preco + j11.preco + j12.preco) PRICE_TEAM,
j1.clube,
j1.nome,
j1.posicao,
j2.clube,
j2.nome,
j2.posicao,
j3.clube,
j3.nome,
j3.posicao,
j4.clube,
j4.nome,
j4.posicao,
j5.clube,
j5.nome,
j5.posicao,
j6.clube,
j6.nome,
j6.posicao,
j7.clube,
j7.nome,
j7.posicao,
j8.clube,
j8.nome,
j8.posicao,
j9.clube,
j9.nome,
j9.posicao,
j10.clube,
j10.nome,
j10.posicao,
j11.clube,
j11.nome,
j11.posicao,
j12.clube,
j12.nome,
j12.posicao
from (select * from jogadores where id = 324) j1,
(select * from jogadores where id = 325) j2,
(select * from jogadores where id = 381) j3,
(select * from jogadores where id = 384) j4,
(select * from jogadores where id = 385) j5,
(select * from jogadores where id = 388) j6,
(select * from jogadores where id = 378) j7,
(select * from jogadores where id = 379) j8,
(select * from jogadores where id = 421) j9,
(select * from jogadores where id = 425) j10,
(select * from jogadores where id = 345) j11,
(select * from jogadores where id = 401) j12;
```

Now I have my selection of the 12 most effective players for my team.

 FORMATION 442 GREATEST_RESULT 134,71 PRICE_TEAM 175,7 CLUBE Atlético-PR NOME Éderson POSICAO ATA CLUBE Vitória NOME Maxi Biancucchi POSICAO ATA CLUBE Internacional NOME Fred POSICAO MEI CLUBE Vasco NOME Carlos Alberto POSICAO MEI CLUBE Cruzeiro NOME Nilton POSICAO MEI CLUBE Corinthians NOME Guilherme POSICAO MEI CLUBE Náutico NOME Maranhão POSICAO LAT CLUBE Internacional NOME Gabriel POSICAO LAT CLUBE Fluminense NOME Digão POSICAO ZAG CLUBE Grêmio NOME Bressan POSICAO ZAG CLUBE Vitória NOME Wilson POSICAO GOL CLUBE Flamengo NOME Jaime De Almeida POSICAO TEC

Filippe

• ###### 5. Re: Processing Cost - How to catch a soccer team with the highest combined score?

The plot thickens. With fixed positions, I would anchor on the combinations of those positions, like this, where I have added the two fixed positions 'CO' coach, 'GK' goalie:

```WITH players_ranked AS (
SELECT id,
position,
price,
avg_points,
appearances,
Row_Number() OVER (ORDER BY price) rnk
FROM players
), rsf (nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
SELECT 0, 0, p1.price + p2.price, p1.avg_points + p1.appearances + p2.avg_points + p2.appearances,
0, 0, 0, 0,
CAST (LPad (p1.id, 3, '0') || LPad (p2.id, 3, '0') AS VARCHAR2(4000)) path
FROM players p1
JOIN players p2
ON p1.position = 'CO'
AND p2.position = 'GK'
UNION ALL
SELECT p.rnk,
r.lev + 1,
r.tot_price + p.price,
r.tot_profit + p.avg_points + p.appearances,
r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
r.path || LPad (p.id, 3, '0')
FROM rsf r
JOIN players_ranked p
ON p.rnk > r.nxt_id
WHERE EXISTS (SELECT 1 FROM formations WHERE
centre_backs >= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
wing_backs  >= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
midfielders  >= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
forwards    >= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
)
AND p.position NOT IN ('CO', 'GK')
AND r.tot_price + p.price <= 200
) SEARCH DEPTH FIRST BY nxt_id SET line_no
, paths_ranked AS (
SELECT tot_price,
tot_profit,
Row_Number () OVER (ORDER BY tot_profit DESC) r_profit,
path
FROM rsf
WHERE lev = 10
), top_ten_paths AS (
SELECT tot_price,
tot_profit,
r_profit,
path,
player_index
FROM paths_ranked
CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL < 13)
WHERE r_profit <= 10
), top_ten_teams AS (
SELECT tot_price,
tot_profit,
r_profit,
path,
player_index,
Substr (path, (player_index - 1) * 3 + 1, 3) player_id
FROM top_ten_paths
)
SELECT t.tot_profit,
t.tot_price,
t.r_profit rnk,
p.position,
t.player_id p_id,
p.player_name,
p.club_name,
p.price,
p.avg_points,
p.appearances apps,
p.avg_points * p.appearances profit
FROM top_ten_teams t
JOIN players p
ON p.id = t.player_id
ORDER BY t.r_profit, t.path, p.position, t.player_index

```

Here is the first ranked team from your sample data. The top ten were returned in 0.6 seconds. Can you supply the complete data set, or a link to it, so I can test properly?

```TOT_PROFIT  TOT_PRICE        RNK PO P_ID PLAYER_NAME          CLUB_NAME            PRICE AVG_POINTS       APPS     PROFIT
---------- ---------- ---------- -- ---- -------------------- --------------- ---------- ---------- ---------- ----------
116.35     146.79          1 CB 001  Bressan              Grêmio               10.85        5.9          4       23.6
003  Cléber               Ponte Preta          14.61       5.78          5       28.9
002  Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
CO 015  Caio Júnior          Vitória               11.4       4.45          5      22.25
FW 011  Chiquinho            Ponte Preta           9.97       4.64          5       23.2
GK 018  Cássio               Corinthians          12.51       3.74          5       18.7
MF 010  Correa               Portuguesa            8.44        5.6          4       22.4
008  Cicinho              Ponte Preta          11.42       4.72          5       23.6
007  Carlos Alberto       Vasco                15.01       6.75          2       13.5
006  Alex                 Coritiba             16.98       5.08          5       25.4
WB 004  Auremir              Náutico               7.73       5.48          4      21.92
005  Carlinhos            Fluminense            12.4       6.93          3      20.79```

I notice I have switched to profit as sum in the body of the query but forgotten to do the same in the main select.

• ###### 6. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Thanks, BredanP. I'm analyzing your query. Follows the data.

esquema table

```prompt PL/SQL Developer import file
prompt Created on quinta-feira, 20 de junho de 2013 by x100370
set feedback off
set define off
prompt Creating ESQUEMA...
create table ESQUEMA
(
ESQUEMA   INTEGER,
ZAGUEIROS INTEGER,
LATERAIS  INTEGER,
MEIAS     INTEGER,
ATACANTES INTEGER,
P1        CHAR(3),
P2        CHAR(3),
P3        CHAR(3),
P4        CHAR(3),
P5        CHAR(3),
P6        CHAR(3),
P7        CHAR(3),
P8        CHAR(3),
P9        CHAR(3),
P10       CHAR(3)
)
;
create index P10_I on ESQUEMA (P10);
create index P1_I on ESQUEMA (P1);
create index P2_I on ESQUEMA (P2);
create index P3_I on ESQUEMA (P3);
create index P4_I on ESQUEMA (P4);
create index P5_I on ESQUEMA (P5);
create index P6_I on ESQUEMA (P6);
create index P7_I on ESQUEMA (P7);
create index P8_I on ESQUEMA (P8);
create index P9_I on ESQUEMA (P9);

prompt Truncating ESQUEMA...
truncate table ESQUEMA;
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (442, 2, 2, 4, 2, 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA');
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (433, 2, 2, 3, 3, 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA', 'ATA');
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (532, 3, 2, 3, 2, 'ZAG', 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA');
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (352, 3, null, 5, 2, 'ZAG', 'ZAG', 'ZAG', 'MEI', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA');
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (343, 3, null, 4, 3, 'ZAG', 'ZAG', 'ZAG', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA', 'ATA');
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (451, 2, 2, 5, 1, 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA');
insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
values (541, 3, 2, 4, 1, 'ZAG', 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA');
commit;
set feedback on
set define on
prompt Done.
```

```prompt PL/SQL Developer import file
prompt Created on quinta-feira, 20 de junho de 2013 by x100370
set feedback off
set define off
(
ID      INTEGER not null,
CLUBE   VARCHAR2(100),
NOME    VARCHAR2(100),
POSICAO CHAR(3),
PRECO   NUMBER(4,2),
MEDIA   NUMBER(4,2),
JOGOS   INTEGER
)
;

insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (324, 'Atlético-PR', 'Éderson', 'ATA', 17.12, 10.12, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (325, 'Vitória', 'Maxi Biancucchi', 'ATA', 19.62, 10.05, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (326, 'Fluminense', 'Rafael Sobis', 'ATA', 23.03, 9.55, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (327, 'Bahia', 'Fernandão', 'ATA', 13.28, 8.22, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (328, 'São Paulo', 'Luis Fabiano', 'ATA', 21.54, 7.58, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (329, 'Botafogo', 'Rafael Marques', 'ATA', 19.74, 6.68, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (330, 'Cruzeiro', 'Dagoberto', 'ATA', 22.11, 5.94, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (331, 'Náutico', 'Rogério', 'ATA', 10.62, 5.7, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (332, 'Flamengo', 'Hernane', 'ATA', 13.87, 4.98, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (333, 'Crisciúma', 'Lins', 'ATA', 18.4, 4.9, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (334, 'Santos', 'Neilton', 'ATA', 6.38, 4.88, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (335, 'Fluminense', 'Samuel', 'ATA', 10.01, 4.87, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (336, 'Ponte Preta', 'Chiquinho', 'ATA', 9.97, 4.64, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (337, 'Atlético-MG', 'Luan', 'ATA', 13.18, 4.55, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (338, 'Ponte Preta', 'William', 'ATA', 13.93, 4.44, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (339, 'Botafogo', 'Vitinho', 'ATA', 10.2, 4.04, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (340, 'Coritiba', 'Deivid', 'ATA', 15.9, 3.76, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (341, 'Grêmio', 'Barcos', 'ATA', 18.96, 3.67, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (342, 'Atlético-MG', 'Jô', 'ATA', 13.93, 3.4, 2);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (343, 'São Paulo', 'Osvaldo', 'ATA', 13.64, 3.12, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (344, 'Cruzeiro', 'Fábio', 'GOL', 20.9, 7.94, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (345, 'Vitória', 'Wilson', 'GOL', 12.39, 7.94, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (346, 'Coritiba', 'Vanderlei', 'GOL', 18.58, 7.76, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (347, 'Atlético-MG', 'Victor', 'GOL', 11.63, 4.67, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (348, 'Bahia', 'Marcelo Lomba', 'GOL', 13.64, 4.5, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (349, 'Botafogo', 'Renan', 'GOL', 6.77, 4.37, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (350, 'Flamengo', 'Felipe', 'GOL', 15.26, 4.14, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (351, 'Grêmio', 'Dida', 'GOL', 11.32, 3.75, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (352, 'Corinthians', 'Cássio', 'GOL', 12.51, 3.74, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (353, 'Vasco', 'Michel Alves', 'GOL', 8.99, 3.48, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (354, 'Crisciúma', 'Bruno', 'GOL', 10.66, 3.2, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (355, 'Internacional', 'Muriel', 'GOL', 9.81, 3.1, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (356, 'Santos', 'Rafael', 'GOL', 17.82, 3, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (357, 'Atlético-PR', 'Weverton', 'GOL', 6.16, 2.48, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (358, 'Fluminense', 'Ricardo Berna', 'GOL', 4.6, 2.42, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (359, 'Portuguesa', 'Gledson', 'GOL', 4.52, 2.1, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (360, 'São Paulo', 'Rogério Ceni', 'GOL', 14.2, 1.17, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (361, 'Portuguesa', 'Ivan', 'LAT', 7.55, 13.2, 1);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (362, 'Vasco', 'Elsinho', 'LAT', 14.68, 8.5, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (363, 'Cruzeiro', 'Egídio', 'LAT', 14.82, 7.52, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (364, 'Fluminense', 'Carlinhos', 'LAT', 12.4, 6.93, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (365, 'Náutico', 'Auremir', 'LAT', 7.73, 5.48, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (366, 'Cruzeiro', 'Mayke', 'LAT', 3.74, 5.25, 2);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (367, 'Portuguesa', 'Luis Ricardo', 'LAT', 8.58, 4.67, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (368, 'Atlético-MG', 'Richarlyson', 'LAT', 10.2, 4.67, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (369, 'Internacional', 'Fabrício', 'LAT', 8.76, 4.57, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (370, 'São Paulo', 'Juan', 'LAT', 7.89, 4.57, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (371, 'São Paulo', 'Paulo Miranda', 'LAT', 10.53, 4.54, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (372, 'Flamengo', 'João Paulo', 'LAT', 7.15, 4.53, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (373, 'São Paulo', 'Rodrigo Caio', 'LAT', 11.92, 4.52, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (374, 'Coritiba', 'Victor Ferraz', 'LAT', 13.04, 4.2, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (375, 'Bahia', 'Jussandro', 'LAT', 6.94, 4.1, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (376, 'Santos', 'Rafael Galhardo', 'LAT', 12.88, 4.04, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (377, 'Goiás', 'William Matheus', 'LAT', 5.87, 4.02, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (378, 'Náutico', 'Maranhão', 'LAT', 6.53, 4.02, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (379, 'Internacional', 'Gabriel', 'LAT', 11.81, 3.38, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (380, 'Goiás', 'Vítor', 'LAT', 8.77, 3.36, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (381, 'Internacional', 'Fred', 'MEI', 30.28, 8.92, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (382, 'Grêmio', 'Zé Roberto', 'MEI', 25.93, 8.78, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (383, 'Internacional', 'Otavinho', 'MEI', 7.62, 8.07, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (384, 'Vasco', 'Carlos Alberto', 'MEI', 15.01, 6.75, 2);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (385, 'Cruzeiro', 'Nilton', 'MEI', 22.39, 6.46, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (386, 'Coritiba', 'Júnior Urso', 'MEI', 14.38, 6.22, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (387, 'Crisciúma', 'João Vitor', 'MEI', 13.27, 6.04, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (388, 'Corinthians', 'Guilherme', 'MEI', 8.83, 5.87, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (389, 'Corinthians', 'Ralf', 'MEI', 19.65, 5.7, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (390, 'Vitória', 'Escudero', 'MEI', 16.38, 5.68, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (391, 'Portuguesa', 'Correa', 'MEI', 8.44, 5.6, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (392, 'Portuguesa', 'Souza', 'MEI', 12.62, 5.17, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (393, 'Coritiba', 'Alex', 'MEI', 16.98, 5.08, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (394, 'Grêmio', 'Souza', 'MEI', 13.8, 4.98, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (395, 'Ponte Preta', 'Cicinho', 'MEI', 11.42, 4.72, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (396, 'Botafogo', 'Fellype Gabriel', 'MEI', 8.6, 4.47, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (397, 'Atlético-PR', 'João Paulo', 'MEI', 10.56, 4.38, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (398, 'Vasco', 'Sandro Silva', 'MEI', 10.76, 4.28, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (399, 'Santos', 'Cícero', 'MEI', 14.15, 4.18, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (400, 'Fluminense', 'Wagner', 'MEI', 8.55, 4.13, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (401, 'Flamengo', 'Jaime De Almeida', 'TEC', 11.56, 8.03, 1);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (402, 'Cruzeiro', 'Marcelo Oliveira', 'TEC', 16.11, 5.43, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (403, 'Fluminense', 'Abel Braga', 'TEC', 17.51, 5.36, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (404, 'Internacional', 'Dunga', 'TEC', 14.22, 4.63, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (405, 'Vitória', 'Caio Júnior', 'TEC', 11.4, 4.45, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (406, 'Grêmio', 'Vanderlei Luxemburgo', 'TEC', 15.77, 4.42, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (407, 'São Paulo', 'Ney Franco', 'TEC', 15.15, 4.39, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (408, 'Náutico', 'Levi Gomes', 'TEC', 7.08, 4.2, 2);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (409, 'Atlético-PR', 'Ricardo Drubscky', 'TEC', 7.96, 3.92, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (410, 'Coritiba', 'Marquinhos Santos', 'TEC', 10.59, 3.89, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (411, 'Vasco', 'Paulo Autuori', 'TEC', 13.13, 3.61, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (412, 'Portuguesa', 'Edson Pimenta', 'TEC', 3.67, 3.26, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (413, 'Botafogo', 'Oswaldo De Oliveira', 'TEC', 10.77, 3.23, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (414, 'Corinthians', 'Tite', 'TEC', 13.68, 3.17, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (415, 'Santos', 'Claudinei Oliveira', 'TEC', 11.92, 3.17, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (416, 'Bahia', 'Cristóvão Borges', 'TEC', 8.27, 2.92, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (417, 'Crisciúma', 'Vadão', 'TEC', 7.04, 2.86, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (418, 'Goiás', 'Enderson Moreira', 'TEC', 6.8, 2.53, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (419, 'Atlético-MG', 'Cuca', 'TEC', 12.62, 2.32, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (420, 'Ponte Preta', 'Zé Sérgio', 'TEC', 6.85, .75, 1);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (421, 'Fluminense', 'Digão', 'ZAG', 9.31, 9.27, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (422, 'Flamengo', 'Samir', 'ZAG', 2.67, 6.8, 1);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (423, 'Cruzeiro', 'Dedé', 'ZAG', 22.54, 6.4, 5);
commit;
prompt 100 records committed...
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (424, 'São Paulo', 'Lúcio', 'ZAG', 21.71, 6.02, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (425, 'Grêmio', 'Bressan', 'ZAG', 10.85, 5.9, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (426, 'Atlético-PR', 'Manoel', 'ZAG', 16.99, 5.88, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (427, 'Ponte Preta', 'Cléber', 'ZAG', 14.61, 5.78, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (428, 'Cruzeiro', 'Bruno Rodrigo', 'ZAG', 15.47, 5.28, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (429, 'Santos', 'Edu Dracena', 'ZAG', 16.82, 4.97, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (430, 'Náutico', 'William Alves', 'ZAG', 5.56, 4.43, 3);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (431, 'Fluminense', 'Gum', 'ZAG', 12.18, 4.22, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (432, 'Flamengo', 'Wallace', 'ZAG', 4.29, 4.2, 2);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (433, 'Náutico', 'João Filipe', 'ZAG', 5.47, 4.1, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (434, 'Grêmio', 'Werley', 'ZAG', 15.9, 4.03, 4);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (435, 'Corinthians', 'Gil', 'ZAG', 13.23, 3.98, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (436, 'Vitória', 'Gabriel Paulista', 'ZAG', 11.77, 3.94, 5);
insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
values (437, 'Goiás', 'Ernando', 'ZAG', 10.24, 3.74, 5);
commit;
set feedback on
set define on
prompt Done.
```

Grateful,

Filippe

• ###### 7. Re: Processing Cost - How to catch a soccer team with the highest combined score?

BrendanP,

Your query is executing for 101 minutes and has not returned.

Will my laptop?

You could run with the data sent?

• ###### 8. Re: Processing Cost - How to catch a soccer team with the highest combined score?

I tried it last night but got a 'could not extend temp space' error. I've also realised for this type of problem, where the number of items is fixed, you could  join 12 table instances instead of using recursion - but it gets pretty messy. Am also wondering whether a non-SQL solution might be necessary for performance, although likely more complicated. I'll look further as I get time. You might want to try gradually scaling up the problem size - would be interested to hear how you get on.

• ###### 9. Re: Processing Cost - How to catch a soccer team with the highest combined score?

I'm curious to find a solution. Analyzing ...

Thanks

Filippe

• ###### 10. Re: Processing Cost - How to catch a soccer team with the highest combined score?

The solution I posted earlier using recursive SQL gives the guaranteed best solution but, like any guaranteed method, will take a long time for these computationally hard problems as the problem size goes up. It can be modified to explore promising paths early while saving time by ignoring poor-seeming paths. This results in much improved performance but with the solutions no longer guaranteed to be the best. I rank the partial solutions in the recursive branch projection and limit the paths progressed to the best :KEEP_NUM. On OP's full data set (I changed a few details), I get the same best 10 using :KEEP_NUM of 100 and 1000, in 3s and 30s respectively.

This approach can also be used for other combinatorial problems.

Top ten solutions

```
WITH players_ranked AS (
SELECT id,
position,
price,
avg_points,
appearances,
Row_Number() OVER (ORDER BY avg_points DESC) rnk
FROM players
), rsf (dt, nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
SELECT Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC),
0.0001 * Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC),
0, p1.price + p2.price, p1.avg_points + p2.avg_points,
0, 0, 0, 0,
CAST (LPad (p1.id, 3, '0') || LPad (p2.id, 3, '0') AS VARCHAR2(4000)) path
FROM players p1
JOIN players p2
ON p1.position = 'CO'
AND p2.position = 'GK'
UNION ALL
SELECT Row_Number() OVER (ORDER BY r.tot_profit + p.avg_points DESC),-- * r.dt,
p.rnk,
r.lev + 1,
r.tot_price + p.price,
r.tot_profit + p.avg_points,
r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
r.path || LPad (p.id, 3, '0')
FROM rsf r
JOIN players_ranked p
ON p.rnk > r.nxt_id
WHERE EXISTS (SELECT 1 FROM formations WHERE
centre_backs >= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
wing_backs   >= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
midfielders  >= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
forwards     >= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
)
AND p.position NOT IN ('CO', 'GK')
AND r.tot_price + p.price <= 20000
AND r.dt < :KEEP_NUM
) SEARCH DEPTH FIRST BY nxt_id SET line_no
, paths_ranked AS (
SELECT tot_price,
tot_profit,
Row_Number () OVER (ORDER BY tot_profit DESC) r_profit,
path
FROM rsf
WHERE lev = 10
), top_ten_paths AS (
SELECT tot_price,
tot_profit,
r_profit,
path,
player_index
FROM paths_ranked
CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL < 13)
WHERE r_profit <= 10
), top_ten_teams AS (
SELECT tot_price,
tot_profit,
r_profit,
path,
player_index,
Substr (path, (player_index - 1) * 3 + 1, 3) player_id
FROM top_ten_paths
)
SELECT t.tot_profit,
t.tot_price,
t.r_profit rnk,
p.position,
t.player_id p_id,
p.player_name,
p.club_name,
p.price,
p.avg_points
FROM top_ten_teams t
JOIN players p
ON p.id = t.player_id
ORDER BY t.r_profit, t.path, p.position, t.player_index
```

Here are the test data and the first of the top ten solutions found:

```Input Formations

ID  CENTRE_BACKS WING_BACKS MIDFIELDERS   FORWARDS
--- ------------ ---------- ----------- ----------
343            3          0           4          3
352            3          0           5          2
433            2          2           3          3
442            2          2           4          2
451            2          2           5          1
532            3          2           3          2
541            3          2           4          1

7 rows selected.

Input Players

ID  CLUB_NAME       PLAYER_NAME          PO      PRICE AVG_POINTS
--- --------------- -------------------- -- ---------- ----------
089 Portuguesa      Edson Pimenta        CO        367        326
054 Goiás           William Matheus      WB        587        402
022 Vitória         Wilson               GK       1239        794
060 Internacional   Otavinho             MF        762        807
004 Bahia           Fernandão            FW       1328        822
055 Náutico         Maranhão             WB        653        402
011 Santos          Neilton              FW        638        488
110 Náutico         João Filipe          CB        547        410
098 Fluminense      Digão                CB        931        927
001 Atlético-PR     Éderson              FW       1712       1012
052 Bahia           Jussandro            WB        694        410
042 Náutico         Auremir              WB        773        548
043 Cruzeiro        Mayke                WB        374        525
008 Náutico         Rogério              FW       1062        570
065 Corinthians     Guilherme            MF        883        587
068 Portuguesa      Correa               MF        844        560
026 Botafogo        Renan                GK        677        437
099 Flamengo        Samir                CB        267        680
040 Cruzeiro        Egídio               WB       1482        752
086 Atlético-PR     Ricardo Drubscky     CO        796        392
107 Náutico         William Alves        CB        556        443
013 Ponte Preta     Chiquinho            FW        997        464
039 Vasco           Elsinho              WB       1468        850
064 Crisciúma       João Vitor           MF       1327        604
102 Grêmio          Bressan              CB       1085        590
063 Coritiba        Júnior Urso          MF       1438        622
048 São Paulo       Paulo Miranda        WB       1053        454
035 Fluminense      Ricardo Berna        GK        460        242
023 Coritiba        Vanderlei            GK       1858        776
046 Internacional   Fabrício             WB        876        457
073 Botafogo        Fellype Gabriel      MF        860        447
074 Atlético-PR     João Paulo           MF       1056        438
072 Ponte Preta     Cicinho              MF       1142        472
002 Vitória         Maxi Biancucchi      FW       1962       1005
094 Crisciúma       Vadão                CO        704        286
034 Atlético-PR     Weverton             GK        616        248
075 Vasco           Sandro Silva         MF       1076        428
016 Botafogo        Vitinho              FW       1020        404
104 Ponte Preta     Cléber               CB       1461        578
109 Flamengo        Wallace              CB        429        420
082 Vitória         Caio Júnior          CO       1140        445
030 Vasco           Michel Alves         GK        899        348
057 Goiás           Vítor                WB        877        336
049 Flamengo        João Paulo           WB        715        453
021 Cruzeiro        Fábio                GK       2090        794
050 São Paulo       Rodrigo Caio         WB       1192        452
095 Goiás           Enderson Moreira     CO        680        253
036 Portuguesa      Gledson              GK        452        210
087 Coritiba        Marquinhos Santos    CO       1059        389
114 Goiás           Ernando              CB       1024        374
009 Flamengo        Hernane              FW       1387        498
093 Bahia           Cristóvão Borges     CO        827        292
038 Portuguesa      Ivan                 WB        755       1320
047 São Paulo       Juan                 WB        789        457
067 Vitória         Escudero             MF       1638        568
103 Atlético-PR     Manoel               CB       1699        588
105 Cruzeiro        Bruno Rodrigo        CB       1547        528
006 Botafogo        Rafael Marques       FW       1974        668
079 Cruzeiro        Marcelo Oliveira     CO       1611        543
041 Fluminense      Carlinhos            WB       1240        693
113 Vitória         Gabriel Paulista     CB       1177        394
003 Fluminense      Rafael Sobis         FW       2303        955
025 Bahia           Marcelo Lomba        GK       1364        450
069 Portuguesa      Souza                MF       1262        517
044 Portuguesa      Luis Ricardo         WB        858        467
081 Internacional   Dunga                CO       1422        463
051 Coritiba        Victor Ferraz        WB       1304        420
024 Atlético-MG     Victor               GK       1163        467
015 Ponte Preta     William              FW       1393        444
053 Santos          Rafael Galhardo      WB       1288        404
112 Corinthians     Gil                  CB       1323        398
031 Crisciúma       Bruno                GK       1066        320
090 Botafogo        Oswaldo De Oliveira  CO       1077        323
070 Coritiba        Alex                 MF       1698        508
029 Corinthians     Cássio               GK       1251        374
076 Santos          Cícero               MF       1415        418
058 Internacional   Fred                 MF       3028        892
012 Fluminense      Samuel               FW       1001        487
066 Corinthians     Ralf                 MF       1965        570
077 Fluminense      Wagner               MF        855        413
084 São Paulo       Ney Franco           CO       1515        439
071 Grêmio          Souza                MF       1380        498
062 Cruzeiro        Nilton               MF       2239        646
056 Internacional   Gabriel              WB       1181        338
100 Cruzeiro        Dedé                 CB       2254        640
005 São Paulo       Luis Fabiano         FW       2154        758
101 São Paulo       Lúcio                CB       2171        602
108 Fluminense      Gum                  CB       1218        422
014 Atlético-MG     Luan                 FW       1318        455
088 Vasco           Paulo Autuori        CO       1313        361
045 Atlético-MG     Richarlyson          WB       1020        467
027 Flamengo        Felipe               GK       1526        414
059 Grêmio          Zé Roberto           MF       2593        878
007 Cruzeiro        Dagoberto            FW       2211        594
010 Crisciúma       Lins                 FW       1840        490
028 Grêmio          Dida                 GK       1132        375
032 Internacional   Muriel               GK        981        310
080 Fluminense      Abel Braga           CO       1751        536
085 Náutico         Levi Gomes           CO        708        420
017 Coritiba        Deivid               FW       1590        376
091 Corinthians     Tite                 CO       1368        317
020 São Paulo       Osvaldo              FW       1364        312
083 Grêmio          Vanderlei Luxemburgo CO       1577        442
111 Grêmio          Werley               CB       1590        403
061 Vasco           Carlos Alberto       MF       1501        675
106 Santos          Edu Dracena          CB       1682        497
033 Santos          Rafael               GK       1782        300
092 Santos          Claudinei Oliveira   CO       1192        317
018 Grêmio          Barcos               FW       1896        367
096 Atlético-MG     Cuca                 CO       1262        232
078 Flamengo        Jaime De AlMFda      CO       1156        803
019 Atlético-MG     Jô                   FW       1393        340
037 São Paulo       Rogério Ceni         GK       1420        117
097 Ponte Preta     Zé Sérgio            CO        685         75

114 rows selected.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
Top ten solutions

TOT_PROFIT  TOT_PRICE        RNK PO P_ID PLAYER_NAME          CLUB_NAME            PRICE AVG_POINTS
---------- ---------- ---------- -- ---- -------------------- --------------- ---------- ----------
10923      19027          1 CB 098  Digão                Fluminense             931        927
099  Samir                Flamengo               267        680
CO 078  Jaime De AlMFda      Flamengo              1156        803
FW 001  Éderson              Atlético-PR           1712       1012
002  Maxi Biancucchi      Vitória               1962       1005
003  Rafael Sobis         Fluminense            2303        955
GK 021  Fábio                Cruzeiro              2090        794
MF 058  Fred                 Internacional         3028        892
059  Zé Roberto           Grêmio                2593        878
060  Otavinho             Internacional          762        807
WB 038  Ivan                 Portuguesa             755       1320
039  Elsinho              Vasco                 1468        850
...
```
• ###### 11. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Returned ORA-01008: not all variables bound ...

player_index?

• ###### 12. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Probably the bind variable I added. Replace it with a number like 100, or do this in sqlplus:

VAR KEEP_NUM NUMBER

BEGIN

:KEEP_NUM := 100;

END;

/

• ###### 13. Re: Processing Cost - How to catch a soccer team with the highest combined score?

I'm doing something wrong...

Not return anything:

```WITH players_ranked AS
(SELECT id,
posicao,
preco,
media,
jogos,
Row_Number() OVER(ORDER BY media DESC) rnk
rsf(dt,
nxt_id,
lev,
tot_price,
tot_profit,
n_cb,
n_wb,
n_mf,
n_fw,
path) AS
(SELECT Row_Number() OVER(ORDER BY(p1.media + p2.media) DESC),
0.0001 * Row_Number() OVER(ORDER BY(p1.media + p2.media) DESC),
0,
p1.preco + p2.preco,
p1.media + p2.media,
0,
0,
0,
0,
ON p1.posicao = 'CO'
AND p2.posicao = 'GK'
UNION ALL
SELECT Row_Number() OVER(ORDER BY r.tot_profit + p.media DESC), -- * r.dt,
p.rnk,
r.lev + 1,
r.tot_price + p.preco,
r.tot_profit + p.media,
r.n_cb + CASE p.posicao
WHEN 'CB' THEN
1
ELSE
0
END,
r.n_wb + CASE p.posicao
WHEN 'WB' THEN
1
ELSE
0
END,
r.n_mf + CASE p.posicao
WHEN 'MF' THEN
1
ELSE
0
END,
r.n_fw + CASE p.posicao
WHEN 'FW' THEN
1
ELSE
0
END,
FROM rsf r
JOIN players_ranked p
ON p.rnk > r.nxt_id
WHERE EXISTS (SELECT 1
FROM esquema
WHERE laterais >= r.n_cb + CASE p.posicao
WHEN 'CB' THEN
1
ELSE
0
END
AND zagueiros >= r.n_wb + CASE p.posicao
WHEN 'WB' THEN
1
ELSE
0
END
AND meias >= r.n_mf + CASE p.posicao
WHEN 'MF' THEN
1
ELSE
0
END
AND atacantes >= r.n_fw + CASE p.posicao
WHEN 'FW' THEN
1
ELSE
0
END)
AND p.posicao NOT IN ('CO', 'GK')
AND r.tot_price + p.preco <= 20000
AND r.dt < 100 --:KEEP_NUM SQLPLUS VAR KEEP_NUM NUMBER BEGIN :KEEP_NUM := 100; END; /
) SEARCH DEPTH FIRST BY nxt_id SET line_no,
paths_ranked AS
(SELECT tot_price,
tot_profit,
Row_Number() OVER(ORDER BY tot_profit DESC) r_profit,
path
FROM rsf
WHERE lev = 10),
top_ten_paths AS
(SELECT tot_price, tot_profit, r_profit, path, player_index -- ***
FROM paths_ranked
CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL < 13)
WHERE r_profit <= 10),
top_ten_teams AS
(SELECT tot_price,
tot_profit,
r_profit,
path,
player_index,
Substr(path, (player_index - 1) * 3 + 1, 3) player_id
FROM top_ten_paths)
SELECT t.tot_profit,
t.tot_price,
t.r_profit   rnk,
p.posicao,
t.player_id  p_id,
p.nome,
p.clube,
p.preco,
p.media
FROM top_ten_teams t
ON p.id = t.player_id
ORDER BY t.r_profit, t.path, p.posicao, t.player_index
```

Filippe

• ###### 14. Re: Processing Cost - How to catch a soccer team with the highest combined score?

Not sure, have you swapped your codes for mine? Anyway, here is the complete code that I have just run on a different computer, and it worked, so you could start there...

```SET TRIMSPOOL ON
SET PAGES 10000
SET lines 1000
SET SERVEROUTPUT ON
SPOOL ..\lst\FF_DDL
SELECT 'Start: ' || To_Char(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

DROP TABLE formations
/
CREATE TABLE formations (
id              VARCHAR2(3) PRIMARY KEY,
centre_backs    INTEGER,
wing_backs      INTEGER,
midfielders     INTEGER,
forwards        INTEGER
)
/
DROP TABLE players
/
CREATE TABLE players (
id              VARCHAR2(3) PRIMARY KEY,
club_name       VARCHAR2(30),
player_name     VARCHAR2(30),
position        VARCHAR2(2),
price           NUMBER,
avg_points      NUMBER,
appearances     NUMBER
)
/
EXECUTE Utils.Clear_Log;
DECLARE

i     PLS_INTEGER := 0;
PROCEDURE Ins_Formation (
p_id             VARCHAR2,
p_centre_backs     PLS_INTEGER,
p_wing_backs     PLS_INTEGER,
p_midfielders     PLS_INTEGER,
p_forwards     PLS_INTEGER) IS
BEGIN

INSERT INTO formations VALUES (p_id, p_centre_backs, p_wing_backs, p_midfielders, p_forwards);

END Ins_Formation;

PROCEDURE Ins_Player (p_club_name    VARCHAR2,
p_player_name  VARCHAR2,
p_position     VARCHAR2,
p_price        NUMBER,
p_avg_points   NUMBER,
p_appearances  PLS_INTEGER) IS
BEGIN

i := i + 1;
INSERT INTO players VALUES (LPad (i, 3, '0'), p_club_name, p_player_name, p_position, 100*p_price, 100*p_avg_points, 100*p_appearances);

END Ins_Player;

BEGIN

DELETE formations;
Ins_Formation ('442', 2, 2, 4, 2);
Ins_Formation ('433', 2, 2, 3, 3);
Ins_Formation ('532', 3, 2, 3, 2);
Ins_Formation ('352', 3, 0, 5, 2);
Ins_Formation ('343', 3, 0, 4, 3);
Ins_Formation ('451', 2, 2, 5, 1);
Ins_Formation ('541', 3, 2, 4, 1);

DELETE players;
Ins_Player ('Atlético-PR', 'Éderson', 'FW', 17.12, 10.12, 5);
Ins_Player ('Vitória', 'Maxi Biancucchi', 'FW', 19.62, 10.05, 4);
Ins_Player ('Fluminense', 'Rafael Sobis', 'FW', 23.03, 9.55, 4);
Ins_Player ('Bahia', 'Fernandão', 'FW', 13.28, 8.22, 5);
Ins_Player ('São Paulo', 'Luis Fabiano', 'FW', 21.54, 7.58, 4);
Ins_Player ('Botafogo', 'Rafael Marques', 'FW', 19.74, 6.68, 5);
Ins_Player ('Cruzeiro', 'Dagoberto', 'FW', 22.11, 5.94, 5);
Ins_Player ('Náutico', 'Rogério', 'FW', 10.62, 5.7, 5);
Ins_Player ('Flamengo', 'Hernane', 'FW', 13.87, 4.98, 5);
Ins_Player ('Crisciúma', 'Lins', 'FW', 18.4, 4.9, 5);
Ins_Player ('Santos', 'Neilton', 'FW', 6.38, 4.88, 4);
Ins_Player ('Fluminense', 'Samuel', 'FW', 10.01, 4.87, 3);
Ins_Player ('Ponte Preta', 'Chiquinho', 'FW', 9.97, 4.64, 5);
Ins_Player ('Atlético-MG', 'Luan', 'FW', 13.18, 4.55, 4);
Ins_Player ('Ponte Preta', 'William', 'FW', 13.93, 4.44, 5);
Ins_Player ('Botafogo', 'Vitinho', 'FW', 10.2, 4.04, 5);
Ins_Player ('Coritiba', 'Deivid', 'FW', 15.9, 3.76, 5);
Ins_Player ('Grêmio', 'Barcos', 'FW', 18.96, 3.67, 4);
Ins_Player ('Atlético-MG', 'Jô', 'FW', 13.93, 3.4, 2);
Ins_Player ('São Paulo', 'Osvaldo', 'FW', 13.64, 3.12, 5);
Ins_Player ('Cruzeiro', 'Fábio', 'GK', 20.9, 7.94, 5);
Ins_Player ('Vitória', 'Wilson', 'GK', 12.39, 7.94, 5);
Ins_Player ('Coritiba', 'Vanderlei', 'GK', 18.58, 7.76, 5);
Ins_Player ('Atlético-MG', 'Victor', 'GK', 11.63, 4.67, 4);
Ins_Player ('Bahia', 'Marcelo Lomba', 'GK', 13.64, 4.5, 5);
Ins_Player ('Botafogo', 'Renan', 'GK', 6.77, 4.37, 4);
Ins_Player ('Flamengo', 'Felipe', 'GK', 15.26, 4.14, 5);
Ins_Player ('Grêmio', 'Dida', 'GK', 11.32, 3.75, 4);
Ins_Player ('Corinthians', 'Cássio', 'GK', 12.51, 3.74, 5);
Ins_Player ('Vasco', 'Michel Alves', 'GK', 8.99, 3.48, 5);
Ins_Player ('Crisciúma', 'Bruno', 'GK', 10.66, 3.2, 5);
Ins_Player ('Internacional', 'Muriel', 'GK', 9.81, 3.1, 4);
Ins_Player ('Santos', 'Rafael', 'GK', 17.82, 3, 5);
Ins_Player ('Atlético-PR', 'Weverton', 'GK', 6.16, 2.48, 5);
Ins_Player ('Fluminense', 'Ricardo Berna', 'GK', 4.6, 2.42, 4);
Ins_Player ('Portuguesa', 'Gledson', 'GK', 4.52, 2.1, 4);
Ins_Player ('São Paulo', 'Rogério Ceni', 'GK', 14.2, 1.17, 4);
Ins_Player ('Portuguesa', 'Ivan', 'WB', 7.55, 13.2, 1);
Ins_Player ('Vasco', 'Elsinho', 'WB', 14.68, 8.5, 4);
Ins_Player ('Cruzeiro', 'Egídio', 'WB', 14.82, 7.52, 5);
Ins_Player ('Fluminense', 'Carlinhos', 'WB', 12.4, 6.93, 3);
Ins_Player ('Náutico', 'Auremir', 'WB', 7.73, 5.48, 4);
Ins_Player ('Cruzeiro', 'Mayke', 'WB', 3.74, 5.25, 2);
Ins_Player ('Portuguesa', 'Luis Ricardo', 'WB', 8.58, 4.67, 3);
Ins_Player ('Atlético-MG', 'Richarlyson', 'WB', 10.2, 4.67, 3);
Ins_Player ('Internacional', 'Fabrício', 'WB', 8.76, 4.57, 4);
Ins_Player ('São Paulo', 'Juan', 'WB', 7.89, 4.57, 3);
Ins_Player ('São Paulo', 'Paulo Miranda', 'WB', 10.53, 4.54, 5);
Ins_Player ('Flamengo', 'João Paulo', 'WB', 7.15, 4.53, 3);
Ins_Player ('São Paulo', 'Rodrigo Caio', 'WB', 11.92, 4.52, 5);
Ins_Player ('Coritiba', 'Victor Ferraz', 'WB', 13.04, 4.2, 5);
Ins_Player ('Bahia', 'Jussandro', 'WB', 6.94, 4.1, 5);
Ins_Player ('Santos', 'Rafael Galhardo', 'WB', 12.88, 4.04, 5);
Ins_Player ('Goiás', 'William Matheus', 'WB', 5.87, 4.02, 5);
Ins_Player ('Náutico', 'Maranhão', 'WB', 6.53, 4.02, 5);
Ins_Player ('Internacional', 'Gabriel', 'WB', 11.81, 3.38, 5);
Ins_Player ('Goiás', 'Vítor', 'WB', 8.77, 3.36, 5);
Ins_Player ('Internacional', 'Fred', 'MF', 30.28, 8.92, 5);
Ins_Player ('Grêmio', 'Zé Roberto', 'MF', 25.93, 8.78, 4);
Ins_Player ('Internacional', 'Otavinho', 'MF', 7.62, 8.07, 3);
Ins_Player ('Vasco', 'Carlos Alberto', 'MF', 15.01, 6.75, 2);
Ins_Player ('Cruzeiro', 'Nilton', 'MF', 22.39, 6.46, 5);
Ins_Player ('Coritiba', 'Júnior Urso', 'MF', 14.38, 6.22, 5);
Ins_Player ('Crisciúma', 'João Vitor', 'MF', 13.27, 6.04, 5);
Ins_Player ('Corinthians', 'Guilherme', 'MF', 8.83, 5.87, 4);
Ins_Player ('Corinthians', 'Ralf', 'MF', 19.65, 5.7, 5);
Ins_Player ('Vitória', 'Escudero', 'MF', 16.38, 5.68, 5);
Ins_Player ('Portuguesa', 'Correa', 'MF', 8.44, 5.6, 4);
Ins_Player ('Portuguesa', 'Souza', 'MF', 12.62, 5.17, 4);
Ins_Player ('Coritiba', 'Alex', 'MF', 16.98, 5.08, 5);
Ins_Player ('Grêmio', 'Souza', 'MF', 13.8, 4.98, 4);
Ins_Player ('Ponte Preta', 'Cicinho', 'MF', 11.42, 4.72, 5);
Ins_Player ('Botafogo', 'Fellype Gabriel', 'MF', 8.6, 4.47, 4);
Ins_Player ('Atlético-PR', 'João Paulo', 'MF', 10.56, 4.38, 5);
Ins_Player ('Vasco', 'Sandro Silva', 'MF', 10.76, 4.28, 5);
Ins_Player ('Santos', 'Cícero', 'MF', 14.15, 4.18, 5);
Ins_Player ('Fluminense', 'Wagner', 'MF', 8.55, 4.13, 3);
Ins_Player ('Flamengo', 'Jaime De AlMFda', 'CO', 11.56, 8.03, 1);
Ins_Player ('Cruzeiro', 'Marcelo Oliveira', 'CO', 16.11, 5.43, 5);
Ins_Player ('Fluminense', 'Abel Braga', 'CO', 17.51, 5.36, 4);
Ins_Player ('Internacional', 'Dunga', 'CO', 14.22, 4.63, 5);
Ins_Player ('Vitória', 'Caio Júnior', 'CO', 11.4, 4.45, 5);
Ins_Player ('Grêmio', 'Vanderlei Luxemburgo', 'CO', 15.77, 4.42, 4);
Ins_Player ('São Paulo', 'Ney Franco', 'CO', 15.15, 4.39, 5);
Ins_Player ('Náutico', 'Levi Gomes', 'CO', 7.08, 4.2, 2);
Ins_Player ('Atlético-PR', 'Ricardo Drubscky', 'CO', 7.96, 3.92, 5);
Ins_Player ('Coritiba', 'Marquinhos Santos', 'CO', 10.59, 3.89, 5);
Ins_Player ('Vasco', 'Paulo Autuori', 'CO', 13.13, 3.61, 5);
Ins_Player ('Portuguesa', 'Edson Pimenta', 'CO', 3.67, 3.26, 4);
Ins_Player ('Botafogo', 'Oswaldo De Oliveira', 'CO', 10.77, 3.23, 5);
Ins_Player ('Corinthians', 'Tite', 'CO', 13.68, 3.17, 5);
Ins_Player ('Santos', 'Claudinei Oliveira', 'CO', 11.92, 3.17, 3);
Ins_Player ('Bahia', 'Cristóvão Borges', 'CO', 8.27, 2.92, 5);
Ins_Player ('Crisciúma', 'Vadão', 'CO', 7.04, 2.86, 5);
Ins_Player ('Goiás', 'Enderson Moreira', 'CO', 6.8, 2.53, 5);
Ins_Player ('Atlético-MG', 'Cuca', 'CO', 12.62, 2.32, 4);
Ins_Player ('Ponte Preta', 'Zé Sérgio', 'CO', 6.85, .75, 1);
Ins_Player ('Fluminense', 'Digão', 'CB', 9.31, 9.27, 3);
Ins_Player ('Flamengo', 'Samir', 'CB', 2.67, 6.8, 1);
Ins_Player ('Cruzeiro', 'Dedé', 'CB', 22.54, 6.4, 5);
Ins_Player ('São Paulo', 'Lúcio', 'CB', 21.71, 6.02, 5);
Ins_Player ('Grêmio', 'Bressan', 'CB', 10.85, 5.9, 4);
Ins_Player ('Atlético-PR', 'Manoel', 'CB', 16.99, 5.88, 5);
Ins_Player ('Ponte Preta', 'Cléber', 'CB', 14.61, 5.78, 5);
Ins_Player ('Cruzeiro', 'Bruno Rodrigo', 'CB', 15.47, 5.28, 5);
Ins_Player ('Santos', 'Edu Dracena', 'CB', 16.82, 4.97, 3);
Ins_Player ('Náutico', 'William Alves', 'CB', 5.56, 4.43, 3);
Ins_Player ('Fluminense', 'Gum', 'CB', 12.18, 4.22, 4);
Ins_Player ('Flamengo', 'Wallace', 'CB', 4.29, 4.2, 2);
Ins_Player ('Náutico', 'João Filipe', 'CB', 5.47, 4.1, 4);
Ins_Player ('Grêmio', 'Werley', 'CB', 15.9, 4.03, 4);
Ins_Player ('Corinthians', 'Gil', 'CB', 13.23, 3.98, 5);
Ins_Player ('Vitória', 'Gabriel Paulista', 'CB', 11.77, 3.94, 5);
Ins_Player ('Goiás', 'Ernando', 'CB', 10.24, 3.74, 5);
--  DELETE players WHERE player_name > 'Cristóvão Borges';--'Gil';-- 'Cássio';

/*  Ins_Player ('Grêmio',            'Bressan',          'CB',     '1085',    '590',     '4');
Ins_Player ('Cruzeiro',            'Bruno Rodrigo',     'CB',     '1547',    '528',     '5');
Ins_Player ('Ponte Preta',     'Cléber',          'CB',     '1461',    '578',     '5');

Ins_Player ('Náutico',            'Auremir',          'WB',     '773',     '548',     '4');
Ins_Player ('Fluminense',            'Carlinhos',          'WB',     '1240',    '693',     '3');

Ins_Player ('Coritiba',            'Alex',          'MF',     '1698',    '508',     '5');
Ins_Player ('Vasco',            'Carlos Alberto',     'MF',     '1501',    '675',     '2');
Ins_Player ('Ponte Preta',     'Cicinho',          'MF',     '1142',    '472',     '5');
Ins_Player ('Santos',            'Claudinei Oliveira','CO',     '1192',    '317',     '3');
Ins_Player ('Portuguesa',            'Correa',          'MF',     '844',     '560',     '4');

Ins_Player ('Ponte Preta',     'Chiquinho',          'FW',     '997',     '464',     '5');
Ins_Player ('Grêmio',            'Barcos',          'FW',     '1896',    '367',     '4');

Ins_Player ('Bahia',            'Cristóvão Borges',     'CO',     '827',     '292',     '5');
Ins_Player ('Atlético-MG',     'Cuca',          'CO',     '1262',    '232',     '4');
Ins_Player ('Vitória',            'Caio Júnior',     'CO',     '1140',    '445',     '5');
Ins_Player ('Fluminense',            'Abel Braga',          'CO',     '1751',    '536',     '4');

Ins_Player ('Crisciúma',            'Bruno',          'GK',     '1066',    '320',     '5');
Ins_Player ('Corinthians',     'Cássio',          'GK',     '1251',    '374',     '5');
*/
END;
/

PROMPT Input Formations
SELECT *
FROM formations
ORDER BY 1
/
COLUMN club_name FORMAT A15
COLUMN player_name FORMAT A20
PROMPT Input Players
SELECT id,
club_name,
player_name,
position,
price,
avg_points,
appearances,
avg_points * appearances tot_points,
Round (avg_points * appearances / price, 2) utility,
Rank() OVER (ORDER BY avg_points * appearances DESC) t_rank,
Rank() OVER (ORDER BY price) p_rank
FROM players
ORDER BY avg_points * appearances / price DESC
/
SET TIMING ON
COLUMN path FORMAT A30
COLUMN node FORMAT A10
COLUMN p_id FORMAT A4
BREAK ON tot_profit ON tot_price ON rnk ON position
VAR KEEP_NUM NUMBER
BEGIN
:KEEP_NUM := 100;
END;
/
PROMPT Top ten solutions
WITH players_ranked AS (
SELECT id,
position,
price,
avg_points,
appearances,
Row_Number() OVER (ORDER BY avg_points DESC) rnk
FROM players
), rsf (dt, nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
SELECT Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC),
0.0001 * Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC),
0, p1.price + p2.price, p1.avg_points + p2.avg_points,
0, 0, 0, 0,
CAST (LPad (p1.id, 3, '0') || LPad (p2.id, 3, '0') AS VARCHAR2(4000)) path
FROM players p1
JOIN players p2
ON p1.position = 'CO'
AND p2.position = 'GK'
UNION ALL
SELECT Row_Number() OVER (ORDER BY r.tot_profit + p.avg_points DESC),-- * r.dt,
p.rnk,
r.lev + 1,
r.tot_price + p.price,
r.tot_profit + p.avg_points,
r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
r.path || LPad (p.id, 3, '0')
FROM rsf r
JOIN players_ranked p
ON p.rnk > r.nxt_id
WHERE EXISTS (SELECT 1 FROM formations WHERE
centre_backs >= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
wing_backs   >= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
midfielders  >= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
forwards     >= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
)
AND p.position NOT IN ('CO', 'GK')
AND r.tot_price + p.price ```