MySQL Get Latest Entry For All Stores

Question

I am trying to get the latest rows from a table that records the cumulative sales total for each 15 minutes.

The basic layout of the table is

StoreID, Time, Sales, Tax, Date

I'd like to be able to return the latest records for each store id. The query I've tried so far is :

SELECT t.StoreID, MAX(t.Time) as 'Last Reported', t.Sales+t.Tax as Sales, t.Date FROM ( SELECT * FROM trickledata WHERE Date = '20180724' ) t GROUP BY t.StoreID;

This works, however it doesn't return the corresponding sales for the time. E.g.

+---------+---------------+-------+------------+ | StoreID | Last Reported | Sales | Date | +---------+---------------+-------+------------+ | 100 | 11:45:00 | 0.00 | 2018-07-24 | | 111 | 12:00:00 | 0.00 | 2018-07-24 | | 115 | 12:00:00 | 0.00 | 2018-07-24 | | 121 | 12:00:00 | 0.00 | 2018-07-24 | | 122 | 12:00:00 | 0.00 | 2018-07-24 | | 123 | 12:00:00 | 0.00 | 2018-07-24 | | 124 | 12:00:00 | 0.00 | 2018-07-24 | | 125 | 12:00:00 | 0.00 | 2018-07-24 | | 126 | 12:00:00 | 0.00 | 2018-07-24 | | 127 | 12:00:00 | 0.00 | 2018-07-24 | | 128 | 12:00:00 | 0.00 | 2018-07-24 | | 129 | 12:00:00 | 0.00 | 2018-07-24 | | 130 | 12:00:00 | 0.00 | 2018-07-24 | | 131 | 12:00:00 | 0.00 | 2018-07-24 | | 135 | 12:00:00 | 0.00 | 2018-07-24 | | 137 | 12:00:00 | 0.00 | 2018-07-24 | | 138 | 12:00:00 | 0.00 | 2018-07-24 | | 141 | 12:00:00 | 0.00 | 2018-07-24 | | 150 | 12:00:00 | 0.00 | 2018-07-24 | | 160 | 12:00:00 | 0.00 | 2018-07-24 | | 164 | 12:00:00 | 0.00 | 2018-07-24 |

But there have been sales at that time, because when I query that specific date and time for store 121 I get:

+---------+------------+----------+-------+------+ | StoreID | Date | Time | Sales | Tax | +---------+------------+----------+-------+------+ | 121 | 2018-07-24 | 12:00:00 | 65.79 | 4.55 | +---------+------------+----------+-------+------+

CREATE TABLE Statement:

``CREATE TABLE `trickledata` (
    `StoreID` INT(11) NULL DEFAULT NULL,
    `Date` DATE NULL DEFAULT NULL,
    `Time` TIME NULL DEFAULT NULL,
    `Sales` DECIMAL(10,2) NULL DEFAULT NULL,
    `Tax` DECIMAL(10,2) NULL DEFAULT NULL,
    UNIQUE INDEX `Date` (`Date`, `Time`, `StoreID`),
    INDEX `by_date` (`Date`),
    INDEX `by_store` (`StoreID`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
``

Sample Data: INSERT LOW_PRIORITY IGNORE INTO `trickle`.`trickledata_copy` (`StoreID`, `Date`, `Time`, `Sales`, `Tax`) VALUES ('0100', '20180724', '120000', '212.63', '15.37'), ('0100', '20180724', '114500', '212.63', '15.37'), ('0100', '20180724', '113000', '212.63', '15.37'), ('0100', '20180724', '111500', '212.63', '15.37'), ('0100', '20180724', '110000', '212.63', '15.37'), ('0100', '20180724', '104500', '212.63', '15.37'), ('0100', '20180724', '103000', '212.63', '15.37'), ('0100', '20180724', '101500', '.00', '.00'), ('0100', '20180724', '100000', '.00', '.00'), ('0100', '20180724', '94500', '.00', '.00'), ('0100', '20180724', '93000', '.00', '.00'), ('0100', '20180724', '91500', '.00', '.00'), ('0100', '20180724', '90000', '.00', '.00'), ('0100', '20180724', '84500', '.00', '.00'), ('0100', '20180724', '83000', '.00', '.00'), ('0100', '20180724', '81500', '.00', '.00'), ('0100', '20180724', '80000', '.00', '.00'), ('0100', '20180724', '74500', '.00', '.00'), ('0100', '20180723', '253000', '986.07', '71.44'), ('0100', '20180723', '251500', '986.07', '71.44'), ('0100', '20180723', '250000', '986.07', '71.44'), ('0100', '20180723', '244500', '986.07', '71.44'), ('0100', '20180723', '243000', '986.07', '71.44'), ('0100', '20180723', '241500', '986.07', '71.44'), ('0100', '20180723', '240000', '986.07', '71.44'), ('0100', '20180723', '234500', '986.07', '71.44'), ('0100', '20180723', '233000', '986.07', '71.44'), ('0100', '20180723', '231500', '986.07', '71.44'), ('0100', '20180723', '230000', '986.07', '71.44'), ('0100', '20180723', '224500', '986.07', '71.44'), ('0100', '20180723', '223000', '986.07', '71.44'), ('0100', '20180723', '221500', '986.07', '71.44'), ('0100', '20180723', '220000', '986.07', '71.44'), ('0100', '20180723', '214500', '986.07', '71.44'), ('0100', '20180723', '213000', '986.07', '71.44'), ('0100', '20180723', '120000', '986.07', '71.44'), ('0100', '20180723', '210000', '986.07', '71.44'), ('0100', '20180723', '204500', '986.07', '71.44');

Edit #2: Using that sample data and running the query produces the 0.00 bug. But running SELECT * FROM trickledata WHERE Time = '120000 Produces a result of $212.63

Also my MariaDB version is 10.1.29

Edit #3: I tried the following:

''SELECT t.* FROM trickledata t
    -> JOIN (
    -> SELECT StoreID, MAX(Time) AS latest, Sales, Date
    -> FROM trickledata
    -> WHERE Date = '20180724'
    -> GROUP BY StoreID
    -> ) m ON t.StoreID = m.StoreID AND t.Time = m.latest;''

However, it returns every date i have at noon. I just want the data from today.

Edit #4: I figured out if i delete the Unique index, my initial query works as expected. But I don't understand why. Also I need that unique index to prevent duplicates from being added, as this is reading the information from a csv file.

Please provide the structure of the tables as `CREATE TABLE` statements along with some sample data as `INSERT INTO` statements. Add the expected result with that sample data. Please also consider to mention the version of MariaDB you're using. — sticky bit, Jul 24 '18 at 16:45
I explored that tag and tried some of the answers, however it either gives me every possible datapoint at that time, even when i sort by date, or it gives me the bug again. — Jarrett Duskey, Jul 24 '18 at 17:08

score 0 · Accepted Answer · answered Jul 24 '18 at 17:17

0

Try to filter the time using a correlated subquery, that gets the maximum time for a day and store.

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

If there a duplicates (two or more reports on the same time for the same store on the same day) they will be shown. If you want to rule them out you have to define another criteria that should define an additional order on the reports.

answered Jul 24 '18 at 17:17

sticky bit

36,626
12
31
42

This works on my original dataset. I have a unique key in place to prevent duplicates from being added. I have a few questions though. How does it handle when one store reports later than the others? – Jarrett Duskey Jul 24 '18 at 17:23
@JarrettDuskey: I will take the latest report for every store. Say store 1's last reported is from 09:00 and store 2's from 15:00, it will show the figures for store 1 from 09:00 and the figures from store 2 at 15:00. – sticky bit Jul 24 '18 at 17:34
Okay, that makes sense. Do you know why my original query worked when I didn't have a unique key? – Jarrett Duskey Jul 24 '18 at 17:57
@JarrettDuskey: Your original query is ill formed anyway. Not all columns in the list of columns are either an argument to an aggregation function nor in the `GROUP BY` clause. When [`ONLY_FULL_GROUP_BY`](https://mariadb.com/kb/en/library/sql-mode/#setting-sql_mode) isn't set, MariaDB (and MySQL) accepts that but with possibly strange results. Maybe that's the cause for your 0s too. – sticky bit Jul 24 '18 at 21:01

MySQL Get Latest Entry For All Stores

1 Answers1