Get number of elements depending on the maximum number of elements per group [closed]

Navigation
-1

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

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

For example, num2 = 3 has num3 max 4 , that is, when it 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);
    
asked by anonymous 06.12.2016 / 23:51

2 answers

2

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

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 want to use subquery only in WHERE you should make a CROSS JOIN that will aggregate all information in the other table and restrict it to 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
    
07.12.2016 / 14:13
5

Based on the response (from Bacco) to question , I set up a generator to repeat the aquarium as sample_id , 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)

Notes

  

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

     

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

    
07.12.2016 / 13:54
Can anyone help me to correct this JavaScript? [closed] Change the background color of an element when scrolling the page