Get the largest elements of a SQL column

Question

Get the largest elements of a SQL column

Navigation

#1 by (2 votes)

1

I have 3 tables and I have the following question to solve: "Get the" name "with the highest" perce "related to" num2. "Enter" num2 "through" Name2 "

 -- 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');


-- C (num5,num3,name2,num4)
insert into C values (2,1,'Cao',9);
insert into C values (1,2,'Gato',8);
insert into C values (1,3,'Golfinho',7);
insert into C values (3,4,'Peixe',11);

-- D (Num5, Num2, perce)
insert into D values(1,1,100);
insert into D  values(2,1,100);
insert into D values(1,3,10);
insert into D values(3,3,20);
insert into D values(2,2,60);
insert into D values(3,1,50);
insert into D values(2,3,70);
insert into D values(3,2,40);
insert into D values(2,1,0);

mysql sql

asked by anonymous 01.12.2016 / 00:45

1 answer

Add point to every three numbers (thousands) in a tooltip of the input range? httpget + URI + HttpClient discontinued libraries. How to update code?

score 2 · Accepted Answer

You can use the EXISTS clause in WHERE to test if there is any aquarium with greater suitability and only show the result if it does not exist:

SELECT e.nome_cientifico as especie,
       a.nome as aquario
  FROM especie e
       INNER JOIN aquario_habitat ah ON ah.id_habitat = e.id_habitat
       INNER JOIN aquario a ON a.id_aquario = ah.id_aquario
 WHERE NOT EXISTS(SELECT 1
                     FROM aquario_habitat ah2
                    WHERE ah2.id_habitat = ah.id_habitat -- O que importa é o habitat
                      AND ah2.id_aquario <> ah.id_aquario -- Deve desconsiderar o mesmo aquário
                      AND ah2.adequacao > ah.adequacao); -- Caso existe de adequação maior, este deve ser descartado

Subqueries with EXISTS or NOT EXISTS

If subquery returns some line, EXISTS is TRUE , and NOT EXISTS is FALSE

We get the result (based on the data of your images) :

 ________________________________________________
| especie                  | aquario             |
|________________________________________________|
| Lutra sumatrana          | Vasco da Gama       |
| Hexanchus griseus        | Infante D. Henrique |
| Torpedo torpedo          | Infante D. Henrique |
| Echinaster brasiliensis  | Bartolomeu Dias     |
|________________________________________________|

Using the following creation of schema :

CREATE TABLE aquario(id_aquario  INTEGER,
                     localizacao VARCHAR(100),
                     nome        VARCHAR(100));

CREATE TABLE especie(id_especie      INTEGER,
                     nome_cientifico VARCHAR(100),
                     id_cat          INTEGER,
                     id_habitat      INTEGER);

CREATE TABLE aquario_habitat(id_aquario INTEGER,
                             id_habitat INTEGER,
                             adequacao  INTEGER);

insert into aquario(id_aquario, localizacao, nome)
values(1, 'NO', 'Vasco da Gama'),
      (2, 'N', 'Bartolomeu Dias'),
      (3, 'S', 'Infante D. Henrique');

insert into especie(id_especie, nome_cientifico, id_cat, id_habitat)
values(1, 'Lutra sumatrana', 9, 2),
      (2, 'Hexanchus griseus', 8, 1),
      (3, 'Torpedo torpedo', 7, 1),
      (4, 'Echinaster brasiliensis', 11, 3);

insert into aquario_habitat(id_aquario, id_habitat, adequacao)
values(1, 1, 10),
      (2, 1, 0),
      (3, 1, 100),
      (1, 2, 100),
      (2, 2, 90),
      (3, 2, 90),
      (1, 3, 0),
      (2, 3, 100),
      (3, 3, 0);

EDIT

SELECT DISTINCT A.nome,
                E.nome_cientifico
  FROM Aquario A,
       Especie E,
       Aquario_Habitat AH
 WHERE AH.id_habitat = E.id_habitat
   AND A.id_aquario = AH.id_aquario
   AND NOT EXISTS(SELECT 1
                    FROM aquario_habitat ah2
                   WHERE ah2.id_habitat = ah.id_habitat -- O que importa é o habitat
                     AND ah2.id_aquario <> ah.id_aquario -- Deve desconsiderar o mesmo aquário
                     AND ah2.adequacao > ah.adequacao); -- Caso existe de adequação maior, este deve ser descartado;