I wrote code in MySQL to get the highest death value for each country and its reporting date in MySQL. I am able to get the highest death value but the date value returns an error. This is my MySQL code:
SELECT d.country_name, s.dt, MAX(s.deaths) FROM Demographics d inner JOIN statistics s On d.country_id=s.country_id GROUP BY country_name ORDER BY MAX(s.deaths) DESC;
Code is returning:
Country/Region Name | dt | Max(s.deaths) |
---|---|---|
USA | 2022-02-17 | 3635 |
Germany | 2022-02-17 | 455 |
Bangladesh | 2022-02-17 | 43 |
Here, the dt value is wrong. The result should be like this:
Country/Region Name | dt | Max(s.deaths) |
---|---|---|
USA | 2022-01-26 | 3635 |
Germany | 2022-02-16 | 455 |
Bangladesh | 2022-02-08 | 43 |
This is my sample input:
CREATE TABLE `demographics` ( `country_id` char(3) NOT NULL, `country_name` varchar(20) NOT NULL ); CREATE TABLE `statistics` ( `country_id` char(3) NOT NULL, `dt` date NOT NULL, `confirmed_cases` bigint(20) NOT NULL, `deaths` bigint(20) NOT NULL ); INSERT INTO `demographics` (`country_id`, `country_name`) VALUES ('BGD', 'Bangladesh', 164700000, 148460, 1265), ('DEU', 'Germany', 83200000, 357386, 232), ('USA', ‘United_States’, 329500000, 9834000, 36); INSERT INTO `statistics` (`country_id`, `dt`, `confirmed_cases`, `deaths`) VALUES ('DEU', '2022-02-17', 235626, 261), ('DEU', '2022-02-16', 219972, 455), ('DEU', '2022-02-15', 159217, 243), ('DEU', '2022-02-14', 76465, 246), ('DEU', '2022-02-13', 62841, 42), ('DEU', '2022-02-12', 116810, 58), ('DEU', '2022-01-30', 92921, 42), ('DEU', '2022-01-19', 121952, 258), ('DEU', '2022-01-18', 95098, 199), ('DEU', '2022-01-17', 53916, 143), ('BGD', '2022-02-17', 3539, 20), ('BGD', '2022-02-16', 3929, 15), ('BGD', '2022-02-15', 4746, 34), ('BGD', '2022-02-14', 4692, 19), ('BGD', '2022-02-13', 4838, 28), ('BGD', '2022-02-12', 5023, 20), ('BGD', '2022-02-11', 5268, 27), ('BGD', '2022-02-10', 7264, 41), ('BGD', '2022-02-09', 8016, 33), ('BGD', '2022-02-08', 8354, 43), ('BGD', '2022-02-07', 9369, 38), ('BGD', '2022-02-06', 8345, 29), ('BGD', '2022-01-31', 13501, 31), ('BGD', '2022-01-30', 12183, 34), ('BGD', '2022-01-29', 10378, 21), ('BGD', '2022-01-28', 15440, 20), ('BGD', '2022-01-27', 15807, 15), ('BGD', '2022-01-20', 656, 8), ('BGD', '2022-01-19', 9500, 12), ('BGD', '2022-01-18', 8407, 10), ('BGD', '2022-01-17', 6676, 10), ('USA', '2022-02-17', 115149, 2802), ('USA', '2022-02-16', 141177, 3306), ('USA', '2022-02-15', 111151, 2850), ('USA', '2022-02-14', 87057, 1132), ('USA', '2022-02-13', 88494, 977), ('USA', '2022-02-12', 130169, 1555), ('USA', '2022-02-11', 168153, 2203), ('USA', '2022-02-05', 204770, 1761), ('USA', '2022-02-04', 306311, 2811), ('USA', '2022-02-03', 306317, 3279), ('USA', '2022-02-02', 325193, 3412), ('USA', '2022-02-01', 312473, 3582), ('USA', '2022-01-26', 555623, 3635), ('USA', '2022-01-25', 511029, 2972), ('USA', '2022-01-24', 292907, 1635), ('USA', '2022-01-23', 510878, 1394), ('USA', '2022-01-22', 670427, 1813) ('USA', '2022-01-17', 646886, 1283);
Your query works fine and returns the correct value:
Try deleting and recreating your data.