Select higher value from the latest date

Question

I have a table estoque where records of all supplies are stored, with the date of entry into stock, the supply code and the unit value of the piece, my need would be to take the highest unit value of the most recent date of each supply:

Data     | Suprimento | Valor
08/02/18 |      A     | 150,00
08/02/18 |      A     | 255,00  
06/02/18 |      A     | 300,00
02/02/18 |      B     | 100,00

Return:

Data     | Suprimento | Valor
08/02/18 |      A     | 255,00  
02/02/18 |      B     | 100,00          

In other words, I want to select the item with the highest value of the most recent date, worrying about getting the highest date and the highest value within that date, different from the example cited that only cares about getting the highest amount.

Answer 1

You can search for the highest value by using MAX(Valor) and use uppercase HAVING together with a subquery to bring only the highest values of dates that are equal to the highest dates of each supply:

SELECT 
  Suprimento
  , MAX(Valor)
  , Data
FROM Estoque
GROUP BY Suprimento, Data
HAVING Data = (
  SELECT MAX(Estoque1.Data) 
  FROM Estoque [Estoque1] 
  WHERE Estoque1.suprimento = Estoque.Suprimento
)
ORDER BY Suprimento

Sqlfiddle example Online

Answer 2

SELECT
    aux.*
FROM(
SELECT
    *
FROM estoque
ORDER BY `data` DESC, valor DESC) AS aux
GROUP BY `suprimento`;