Problem in SQL selection

Question

Problem in SQL selection

Navigation

#1 by (3 votes)

1

Because even if I filter to return only the fiat manufacturer, does it return other values?

 SELECT FABRICANTE, MODELO, VALOR_DIARIA FROM VEICULO 
    WHERE VALOR_DIARIA = (SELECT MIN(VALOR_DIARIA) FROM VEICULO WHERE FABRICANTE = 'FIAT')

Result:

FABRICANTE                     MODELO                         VALOR_DIARIA
------------------------------ ------------------------------ ------------
VOLKSWAGEN                     GOL                                      80
FIAT                           PALIO                                    80

mysql sql-server oracle

asked by anonymous 07.04.2018 / 01:49

1 answer

How to create a local SSL certificate in Windows? Factorial double in r with letters for differentiation

score 3 · Accepted Answer

The problem is that you used a subquery to filter the values, which is not necessary. In this case, the daily value of FIAT is equal to that of VOLKSWAGEN, and since the subquery returns "80", it brings both. The correct one would simply be:

SELECT FABRICANTE, MODELO, VALOR_DIARIA FROM VEICULO WHERE FABRICANTE = 'FIAT';

Ideally, you should use a filter that uses fields that are primary keys and avoid filtering directly into text to find exact values. Either way this will work.

In case you need to choose the lowest value, you can add one more criterion, like this:

SELECT FABRICANTE, MODELO, VALOR_DIARIA FROM VEICULO WHERE FABRICANTE = 'FIAT' AND VALOR_DIARIA = (SELECT MIN(VALOR_DIARIA) FROM VEICULO WHERE FABRICANTE = 'FIAT');

Or any other criteria prioritizing the registry you need.

It can also be done by limiting lines, forcing the bank to bring the first line. Like this:

SELECT FABRICANTE, MODELO, VALOR_DIARIA FROM VEICULO WHERE FABRICANTE = 'FIAT' AND VALOR_DIARIA = (SELECT MIN(VALOR_DIARIA) FROM VEICULO WHERE FABRICANTE = 'FIAT') LIMIT 1

Hugs!