Search for lower values of one field according to values of another

Question

I have this data entered in my table.

mysql> SELECT * FROM db_cotacao;

+----+------------+-------+------------+--------+---------+
| id | data       | valor | validade   | idforn | idativo |
+----+------------+-------+------------+--------+---------+
|  1 | 2019-02-10 |    20 | 2020-07-02 |      1 |       1 |
|  2 | 2019-02-10 |    30 | 2020-07-02 |      1 |       2 |
|  3 | 2019-02-10 |    40 | 2020-07-02 |      1 |       3 |
|  4 | 2019-02-10 |     5 | 2020-07-02 |      2 |       1 |
|  5 | 2019-02-10 |     8 | 2020-07-02 |      2 |       2 |
|  6 | 2019-02-10 |     4 | 2020-07-02 |      2 |       3 |
+----+------------+-------+------------+--------+---------+

I’d like to do a search where you can introduce me to a camp group idative with the lowest field values value.

Following use query:

mysql> SELECT idforn, idativo, MIN(valor) AS minimo FROM db_cotacao
    -> GROUP BY idativo;

+--------+---------+--------+
| idforn | idativo | minimo |
+--------+---------+--------+
|      1 |       1 |      5 |
|      1 |       2 |      8 |
|      1 |       3 |      4 |
+--------+---------+--------+

Displays the lowest values correctly. But the field idforn does not correspond to the lowest values, but to the first items grouped.

Anyone can help?

Answer 1

The grouping, unifies in a row the records with the same value(s) defined(s) in the GROUP BY declaration. When Mysql does this, it maintains the first record. Imagine that he holds the first record of that value(s) and discards the others, and just keeps the value of the MIN declaration, until closing a group and so on. In a version of Mariadb (I don’t know specifically which), this also occurs, however it keeps the last record instead of the first.

To solve this, simply create a temporary table, ordering (ASC or DESC depending on which it maintains) the column to be used the MIN and the column to be grouped. Then in a select, use grouping with the MIN command.

See this fiddle.

create table db_cotacao (
  id int primary key, data date, valor decimal(4,2), validade date, idforn int, idativo int,
  index(idativo),
  index(idforn)
);

insert into db_cotacao values
(1, '2019-02-10', 20, '2020-07-02', 1, 1),
(2, '2019-02-10', 30, '2020-07-02', 1, 2),
(3, '2019-02-10', 40, '2020-07-02', 1, 3),
(4, '2019-02-10',  5, '2020-07-02', 2, 1),
(5, '2019-02-10',  8, '2020-07-02', 2, 2),                                    
(6, '2019-02-10',  4, '2020-07-02', 2, 3);

#Usei tabela não temporária devida à restrição do *fiddle*.
CREATE table tbOrdened
SELECT id, idforn, idativo, valor FROM db_cotacao order by valor asc, idativo;

SELECT idforn, idativo, min(valor) AS valor from tbOrdened as tb1 GROUP BY idativo;

Any questions, leave a comment.

Answer 2

You can use internal variables to organize the data the way you want:

SET @row_number = 0;
SET @idativo    = 0;
SET @minimo     = 0;

SELECT x.idforn,
       x.idativo,
       x.minimo
  FROM (
  SELECT @row_number := CASE
                          WHEN @idativo = idativo THEN @row_number + 1
                          ELSE 1
                        END AS sequencia,
         @menor := CASE
                     WHEN @idativo <> idativo OR @minimo > dc.valor THEN dc.valor
                     ELSE @minimo
                   END AS minimo,
         @idativo := idativo,
         dc.*
    FROM db_cotacao dc
   ORDER BY idativo,
            idforn DESC
  ) x
 WHERE x.sequencia = 1
 ORDER BY x.idativo

Resulting in:

| idforn | idativo | minimo |
| ------ | ------- | ------ |
| 2      | 1       | 5      |
| 2      | 2       | 8      |
| 2      | 3       | 4      |

Check the result on DB Fiddle.