-1

I am trying to return the Price data from the latest Date for each Name.

SELECT MAX(Date) AS max_date, Date, Price, Name 
from STOCK_DATA_TRANSPOSED 
group by Name

The output below is the output of the above query. As we see the price data is returned by Date and not max_date.

max_date                    Date        Price   Name    
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    1COV.DE 
2021-04-21 00:00:00 2019-12-31 00:00:00 73.41   AAPL    
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ABB.ST  
2021-04-21 00:00:00 2019-12-31 00:00:00 88.54   ABBV    
2021-04-21 00:00:00 2019-12-31 00:00:00 86.86   ABT 
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    AC.PA   
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ACA.PA  
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ACCD    
2021-04-21 00:00:00 2019-12-31 00:00:00 210.57  ACN 
2021-04-21 00:00:00 2019-12-31 00:00:00 329.81  ADBE    
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ADE.OL  
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ADS.DE  
2021-04-21 00:00:00 2019-12-31 00:00:00 7.64    AGLE    
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    AI.PA   
2021-04-21 00:00:00 2019-12-31 00:00:00 51.33   AIG 
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    AIR.PA  
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    AKRBP.OL    
2021-04-21 00:00:00 2019-12-31 00:00:00 1.75    AKTX    
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ALFA.ST 
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ALIV-SDB.ST 
2021-04-21 00:00:00 2019-12-31 00:00:00 112.45  ALL 
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    ALV.DE  
2021-04-21 00:00:00 2019-12-31 00:00:00 NULL    AMBU-B.CO   
2021-04-21 00:00:00 2019-12-31 00:00:00 241.07  AMGN    
2021-04-21 00:00:00 2019-12-31 00:00:00 229.82  AMT

How can I return the Price data based max_date grouped by Name?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
doomdaam
  • 691
  • 1
  • 6
  • 21
  • There are many answers to this type of question on Stack Overflow already. See for example https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql/1313293#1313293 – Bill Karwin Apr 22 '21 at 14:44

1 Answers1

1

Try this:

SELECT sdt1.Date, sdt1.Price, sdt1.Name
FROM STOCK_DATA_TRANSPOSED AS sdt1
WHERE (sdt1.Date, sdt1.Name) IN
(
    SELECT MAX(sdt2.Date), sdt2.Name
    FROM STOCK_DATA_TRANSPOSED AS sdt2
    GROUP BY sdt2.Name
);

In this way you get the info from STOCK_DATA_TRANSPOSED where Date is the MAX(Date) from each Name. You can see the result in DBFiddle.

Ma3x
  • 516
  • 6
  • 19