The issue lies in the way you’re filtering for the maximum time value for a given day and store using the subquery.

In your initial query, you are grouping by StoreID and then joining it with another table that filters by the same date, which is why you’re getting all dates (noon) from all stores.

Here’s the corrected query:

SELECT 
  t1.storeid AS StoreId,
  t1.time AS LastReportedTime,
  t1.sales + t1.tax AS Sales,
  t1.date AS Date
FROM trickledata t1
WHERE t1.date = '2018-07-24'
AND t1.time = (SELECT max(time) 
               FROM trickledata 
               WHERE date = '2018-07-24' AND storeid = t1.storeid);

However, the above query is still not correct because it’s comparing time with a maximum value of time for each day and store. We need to compare it with the latest time that has been reported.

Here’s another corrected version:

SELECT 
  t1.storeid AS StoreId,
  t1.time AS LastReportedTime,
  t1.sales + t1.tax AS Sales,
  t1.date AS Date
FROM trickledata t1
WHERE t1.date = '2018-07-24'
AND (t1.storeid, t1.time) IN (
  SELECT storeid, time
  FROM trickledata
  WHERE date = '2018-07-24' AND sales + tax > 
    (SELECT MAX(sales + tax) 
     FROM trickledata 
     WHERE date = '2018-07-24' AND storeid = t1.storeid)
);

This query will return only the rows with the latest time for each day and store, ensuring that you don’t get any duplicates.

Last modified on 2023-06-21