There is a table with many rows and fields, among the fields there are fields sub_id , date and status . Is there a way to create a sample of those lines that have the maximum date among the same sub_id , and if it suddenly happens that the maximum date not the same, then choose those who have the maximum status among those who have the same sub_id and the same maximum date ? Now I do this:

 SELECT o.`id`,o.`sub_id`, o.`date`, o.`status` INNER JOIN (SELECT MAX(`date`) `max_date`, `sub_id` FROM `orders` GROUP BY `sub_id`) o2 ON o2.`max_date`=o.`date` AND o2.`sub_id`=o.`sub_id` FROM `orders` o

it seems to work, but if suddenly with the same sub_id there is more than one line with the same date , then the sample selects the "first available" one, and I would like to choose the one with the most status .

Is it possible to do this?

  • and if all of a sudden it turns out that the maximum date is not one, then And if one - all the same, exactly the same should be done, only when adjusted for triviality. In other words, select the records that have the maximum status at the maximum date . - Akina
  • Well, yes, only I don’t understand how to do that ... - Spider

1 answer 1

 SELECT o3.* FROM orders AS o3, ( SELECT o2.sub_id, o2.date, MAX(status) AS status FROM orders AS o2, ( SELECT o1.sub_id, MAX(o1.date) AS date FROM orders AS o1 GROUP BY o1.sub_id ) AS sq1 WHERE o2.sub_id = sq1.sub_id AND o2.date = sq1.date GROUP BY o2.sub_id, o2.date ) AS sq2 WHERE o3.sub_id = sq2.sub_id AND o3.date = sq2.date AND o3.status = sq2.status

How to add LEFT JOIN fields from another table related to this by sub_id?

 SELECT o3.*, sometable.somefield FROM orders AS o3 INNER JOIN ( SELECT o2.sub_id, o2.date, MAX(status) AS status FROM orders AS o2, ( SELECT o1.sub_id, MAX(o1.date) AS date FROM orders AS o1 GROUP BY o1.sub_id ) AS sq1 WHERE o2.sub_id = sq1.sub_id AND o2.date = sq1.date GROUP BY o2.sub_id, o2.date ) AS sq2 ON o3.sub_id = sq2.sub_id AND o3.date = sq2.date AND o3.status = sq2.status LEFT JOIN sometable ON sometable.sub_id = o3.sub_id
  • Thanks, here is how to send LEFT JOIN fields from another table related to this by sub_id ? I put immediately before WHERE , but swears at unexpected values. - Spider