Select highest value from the most recent date

Navigation
0

I have a estoque table where records of all supplies are stored, with the stock-in date , the supply code , and unit value of the part, my need would be to get 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 largest date and the highest value within that date, different from the example mentioned that only cares about getting the most amount. >     

asked by anonymous 08.02.2018 / 14:24

2 answers

1

You can search for the highest value using MAX(Valor) and use HAVING clause together with a subquery to bring only the highest values of dates that are equal to the largest 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 Sample Online

    
08.02.2018 / 15:13
0
SELECT
    aux.*
FROM(
SELECT
    *
FROM estoque
ORDER BY 'data' DESC, valor DESC) AS aux
GROUP BY 'suprimento';
    
08.02.2018 / 14:30
Error in Adding IIS on windows 7 edges of an array N x N