Get record by max(date), where date can be null

Question

I'm trying to select the row with the highest date_end value per user_id . date_end can be null, in this case I assume the current (or a given) date:

data:

CREATE TABLE `prices_test` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `user_id` int(10) NOT NULL,
  `date_end` date DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of prices_test
-- ----------------------------
INSERT INTO `prices_test` VALUES ('2', '2', '2018-11-29', '30000.00');
INSERT INTO `prices_test` VALUES ('3', '6', '2018-07-29', '20000.00');
INSERT INTO `prices_test` VALUES ('4', '18', '2017-12-31', '3999.00');
INSERT INTO `prices_test` VALUES ('7', '18', '2018-03-01', '10000.00');
INSERT INTO `prices_test` VALUES ('10', '6', null, '44000.00');
INSERT INTO `prices_test` VALUES ('13', '62', '2018-08-26', '12000.00');
INSERT INTO `prices_test` VALUES ('16', '62', null, '36500.00');
INSERT INTO `prices_test` VALUES ('17', '68', '2014-06-15', '22000.00');
INSERT INTO `prices_test` VALUES ('18', '69', null, '36500.00');
INSERT INTO `prices_test` VALUES ('19', '60', null, '10000.00');
INSERT INTO `prices_test` VALUES ('20', '68', null, '30000.00');

And the query I'm trying to use:

SELECT t.* 
FROM prices_test t 
INNER JOIN (
  SELECT id, user_id, MAX(IFNULL(date_end, '2019-03-21')) AS date_end
  FROM prices_test GROUP BY id
) AS max USING (id, date_end);

sql fiddle: http://sqlfiddle.com/#!9/9e61e0/4

Problem is that my query seems to ignore the MAX(). It selects user_id with id=3, but i would expect id=10 because IFNULL(date_end, '2019-03-21') gives id=10 a higher date than '2018-07-29' from id=3, right? All the other IDs are also not as expected here.

What am I doing wrong?

which version of MySQL are you using? – Vamsi Prabhala Mar 21 '19 at 07:31 — Vamsi Prabhala, Mar 21 '19 at 07:31

Fahmi · Accepted Answer · 2019-03-21T07:41:50.960

1

use correlated subquery

DEMO

select * from prices_test a
where id in (select max(id) from prices_test b where a.user_id=b.user_id)

OR

select * from prices_test a
    where coalesce(date_end,'2019-03-21') in (select max(coalesce(date_end,'2019-03-21')) from prices_test b where a.user_id=b.user_id)

edited Mar 21 '19 at 07:41

answered Mar 21 '19 at 07:31

Fahmi

37,315
5
22
31

score 1 · Answer 2 · answered Mar 21 '19 at 07:33

You should not use group by id .. in this way you obtain the max value for each id (each rows) and not for each user_id .. try using an aggregation function for id column too

SELECT t.* 
FROM prices_test t 
INNER JOIN (
  SELECT max(id), user_id, MAX(IFNULL(date_end, '2019-03-21')) AS date_end
  FROM prices_test 
  GROUP BY user_id
) AS max USING (id, date_end);

Get record by max(date), where date can be null

2 Answers2