Re: How do i get the max row from a calculated query?
sorry if i am being thick; userid has posted on multiple days so i don't understand why "where userid=170" would limit the days displayed when there is no reference to the date
This is my sample data set:
INSERT INTO `drinkusage` (`drinkusageid`, `drinkid`, `userid`, `amount`, `timeofdrink`, `dateofdrink`) VALUES
(8, 2, 317, 5, NULL, '2020-12-21'),
(9, 5, 317, 6, NULL, '2020-12-21'),
(10, 4, 317, 0, NULL, '2020-12-21'),
(11, 3, 317, 0, NULL, '2020-12-21'),
(12, 1, 317, 2, NULL, '2020-12-21'),
(13, 7, 317, 0, NULL, '2020-12-21'),
(14, 6, 317, 0, NULL, '2020-12-21'),
(15, 2, 317, 5, NULL, '2020-12-22'),
(16, 5, 317, 5, NULL, '2020-12-22'),
(17, 4, 317, 5, NULL, '2020-12-22'),
(18, 3, 317, 4, NULL, '2020-12-22'),
(19, 1, 317, 0, NULL, '2020-12-22'),
(20, 7, 317, 6, NULL, '2020-12-22'),
(21, 6, 317, 6, NULL, '2020-12-22'),
(22, 2, 317, 3, NULL, '2020-12-20'),
(23, 5, 317, 4, NULL, '2020-12-20'),
(24, 4, 317, 0, NULL, '2020-12-20'),
(25, 3, 317, 0, NULL, '2020-12-20'),
(26, 1, 317, 5, NULL, '2020-12-20'),
(27, 7, 317, 1, NULL, '2020-12-20'),
(28, 6, 317, 0, NULL, '2020-12-20'),
(29, 2, 317, 5, NULL, '2020-12-19'),
(30, 5, 317, 6, NULL, '2020-12-19'),
(31, 4, 317, 0, NULL, '2020-12-19'),
(32, 3, 317, 0, NULL, '2020-12-19'),
(33, 1, 317, 2, NULL, '2020-12-19'),
(34, 7, 317, 0, NULL, '2020-12-19'),
(35, 6, 317, 0, NULL, '2020-12-19'),
(36, 2, 317, 5, NULL, '2020-12-17'),
(37, 5, 317, 6, NULL, '2020-12-17'),
(38, 4, 317, 0, NULL, '2020-12-17'),
(39, 3, 317, 0, NULL, '2020-12-17'),
(40, 1, 317, 2, NULL, '2020-12-17'),
(41, 7, 317, 0, NULL, '2020-12-17'),
(42, 6, 317, 0, NULL, '2020-12-17'),
(43, 2, 317, 0, NULL, '2020-12-16'),
(44, 5, 317, 0, NULL, '2020-12-16'),
(45, 4, 317, 6, NULL, '2020-12-16'),
(46, 3, 317, 0, NULL, '2020-12-16'),
(47, 1, 317, 0, NULL, '2020-12-16'),
(48, 7, 317, 0, NULL, '2020-12-16'),
(49, 6, 317, 0, NULL, '2020-12-16'),
(50, 1, 2147483647, 0, NULL, '2021-01-07'),
(51, 2, 2147483647, 0, NULL, '2021-01-07'),
(52, 3, 2147483647, 1, NULL, '2021-01-07'),
(53, 4, 2147483647, 1, NULL, '2021-01-07'),
(54, 5, 2147483647, 0, NULL, '2021-01-07'),
(55, 6, 2147483647, 0, NULL, '2021-01-07'),
(56, 7, 2147483647, 1, NULL, '2021-01-07'),
(57, 1, 2147483647, 0, NULL, '2021-01-08'),
(58, 2, 2147483647, 4, NULL, '2021-01-08'),
(59, 3, 2147483647, 0, NULL, '2021-01-08'),
(60, 4, 2147483647, 0, NULL, '2021-01-08'),
(61, 5, 2147483647, 0, NULL, '2021-01-08'),
(62, 6, 2147483647, 0, NULL, '2021-01-08'),
(63, 7, 2147483647, 0, NULL, '2021-01-08'),
(64, 1, 2147483647, 0, NULL, '2021-01-06'),
(65, 2, 2147483647, 0, NULL, '2021-01-06'),
(66, 3, 2147483647, 2, NULL, '2021-01-06'),
(67, 4, 2147483647, 1, NULL, '2021-01-06'),
(68, 5, 2147483647, 0, NULL, '2021-01-06'),
(69, 6, 2147483647, 0, NULL, '2021-01-06'),
(70, 7, 2147483647, 2, NULL, '2021-01-06'),
(71, 1, 1658945976, 2, NULL, '2021-01-06'),
(72, 2, 1658945976, 3, NULL, '2021-01-06'),
(73, 3, 1658945976, 1, NULL, '2021-01-06'),
(74, 4, 1658945976, 0, NULL, '2021-01-06'),
(75, 5, 1658945976, 0, NULL, '2021-01-06'),
(76, 6, 1658945976, 0, NULL, '2021-01-06'),
(77, 7, 1658945976, 0, NULL, '2021-01-06'),
(78, 1, 1658945976, 2, NULL, '2021-01-05'),
(79, 2, 1658945976, 3, NULL, '2021-01-05'),
(80, 3, 1658945976, 1, NULL, '2021-01-05'),
(81, 4, 1658945976, 0, NULL, '2021-01-05'),
(82, 5, 1658945976, 0, NULL, '2021-01-05'),
(83, 6, 1658945976, 0, NULL, '2021-01-05'),
(84, 7, 1658945976, 0, NULL, '2021-01-05'),
(85, 1, 165894516, 2, NULL, '2021-01-05'),
(86, 1, 2147483, 0, NULL, '2021-01-08'),
(87, 2, 2147483, 4, NULL, '2021-01-08'),
(88, 3, 2147483, 2, NULL, '2021-01-08'),
(89, 4, 2147483, 3, NULL, '2021-01-08'),
(90, 5, 2147483, 0, NULL, '2021-01-08'),
(91, 6, 2147483, 1, NULL, '2021-01-08'),
(92, 7, 2147483, 0, NULL, '2021-01-08'),
(93, 1, 170, 1, NULL, '2021-01-08'),
(94, 2, 170, 0, NULL, '2021-01-08'),
(95, 3, 170, 3, NULL, '2021-01-08'),
(96, 4, 170, 0, NULL, '2021-01-08'),
(97, 5, 170, 0, NULL, '2021-01-08'),
(98, 6, 170, 2, NULL, '2021-01-08'),
(99, 7, 170, 0, NULL, '2021-01-08'),
(100, 1, 170, 0, NULL, '2021-01-07'),
(101, 2, 170, 0, NULL, '2021-01-07'),
(102, 3, 170, 3, NULL, '2021-01-07'),
(103, 4, 170, 0, NULL, '2021-01-07'),
(104, 5, 170, 0, NULL, '2021-01-07'),
(105, 6, 170, 0, NULL, '2021-01-07'),
(106, 7, 170, 0, NULL, '2021-01-07'),
(107, 1, 170, 1, NULL, '2021-01-10'),
(108, 2, 170, 1, NULL, '2021-01-10'),
(109, 3, 170, 2, NULL, '2021-01-10'),
(110, 4, 170, 1, NULL, '2021-01-10'),
(111, 5, 170, 0, NULL, '2021-01-10'),
(112, 6, 170, 1, NULL, '2021-01-10'),
(113, 7, 170, 1, NULL, '2021-01-10');
COMMIT;
and these are the drink types:
INSERT INTO `drinktype` (`drinkid`, `drinkname`, `units`) VALUES
(1, 'Bottle/small can of Strong Beer or Cider', 2),
(2, 'Pint/large can of Strong Beer or Cider', 5),
(3, 'Small Glass of Wine (125ml)', 1),
(4, 'Pint/large can of Normal Beer or Cider', 3),
(5, 'Single Shot (35ml)', 1),
(6, 'Bottle/small can of Normal Beer or Cider', 1.5),
(7, 'Liqueur (25ml)', 1);