To obtain number of elements depending on the maximum number of elements per groups

Question

I have two tables and I intend to get you the name table A depending on the Num3. For example, in the table A, we have the num2 and in the table B the values corresponding to the Num1.

What I want is that the name repeated the number of times corresponding to the maximum number of num3 related to the num2.

For example, the num2 = 3 has as num3 maximum value 4, or is it that he appears 4 times

Using these tables:

-- A (name,Num2, local)
insert into A values ('Favela',1,'WE');
insert into A values ('Lamosa',2,'NA');
insert into A values ('Luz',3,'S0');

-- B (Num1,name,Num2, Num3) 
insert into B values (1,'Maria',1,1);
insert into B values (2,'Jorge',2,1);
insert into B values (3,'Teresa',2,1);
insert into B values (4,'Rui',3,1);
insert into B values (1,'Fran',3,3);
insert into B values (2,'Juliett',3,3);

Answer 1

Based on (Bacco) response to question, I set up a generator to repeat the Aquarium as per the id_especime, see:

SELECT A.NOME 'AQUARIO' FROM
    (SELECT (D1+D2*10+d3*100)+1 AS GERADOR FROM
        (SELECT 0 AS D1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T1,
         (SELECT 0 AS D2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T2,
         (SELECT 0 AS D3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T3) T
    , AQUARIO A
WHERE T.GERADOR <= (SELECT MAX(E1.ID_ESPECIME) 'MAXIMO'
                    FROM ESPECIME E1 
                    WHERE E1.ID_AQUARIO = A.ID_AQUARIO 
                    GROUP BY E1.ID_AQUARIO)

Remarks

Instead of starting at zero (0), I modified it to start at one (1);

The generator as it is will generate numbers from 1 to 1000 - it may not be compatible with your model if the ID_ESPECIME is greater than 1000 (today or in the future);

Answer 2

You can use the following query if there are no "holes" in the field id_especime:

SELECT x.nome
  FROM (SELECT max(e.id_especime) as maximo_id_especime,
               a.nome
          FROM aquario a
               INNER JOIN especime e ON e.id_aquario = a.id_aquario
         GROUP BY a.id_aquario, a.nome) x
       INNER JOIN especime e ON e.id_especime <= x.maximo_id_especime
 GROUP BY x.nome, e.id_especime

---

Edit

After the new information provided in the question, if you would like to use the subquery only in the WHERE you must make a CROSS JOIN which will gather all information from the other table and restrict it in the WHERE.

SELECT a.nome
  FROM aquario a
       CROSS JOIN especime e
 WHERE (SELECT max(e2.id_especime)
          FROM especime e2
         WHERE e2.id_aquario = a.id_aquario) >= e.id_especime
 GROUP BY a.nome, e.id_especime