Table structure:

enter image description here

It is impossible to select entries with the maximum deletion date, which are grouped by the same code, with the condition that they are all deleted. If entries have the same code, but have different deletion statuses, then these entries should not be selected. In this example, you need to select records with id = 3, id = 4. I could only come up with such a query to the database:

SELECT * FROM analyzes_test WHERE code IN (SELECT code FROM analyzes_test GROUP BY code HAVING count(code)>1) AND deleted = (max deleted_date)

But I do not know how to completely substitute the largest date of removal.

  • Well, group by code and select MAX(deleted_date) - Grundy
  • Did as you suggested. Error - # 1111 - Incorrect use of group functions joxi.ru/l2Z5w8zcwVdxPm - tirael8
  • I didn’t suggest that at all :-) - Grundy
  • But I need to select adishniki records, not maximum dates. Maybe I wrote a little wrong in the description. - tirael8
  • (code, deleted) IN (SELECT code, max(deleted) FROM analyzes_test GROUP BY code HAVING count(code)>1) - Mike

1 answer 1

Try this query:

 SELECT t.id FROM analyzes_test t JOIN (SELECT code, max (deleted_date) deleted_date FROM analyzes_test WHERE code NOT IN (SELECT code FROM analyzes_test WHERE deleted_date IS null) AND code IS NOT null GROUP BY code HAVING count (code)>1 ) x ON t.code=x.code AND t.deleted_date=x.deleted_date

Dbfiddle here