vendredi 1 juillet 2016

Query to Find minimum date in group by

Consider the following query; SELECT id, MIN(updated_at) FROM accounts GROUP BY mem_id, account, sub_account HAVING COUNT(*) > 1 Simple statement to find duplicates by grouping the selected fields and printing the id and smallest updated_at value amongst those groupings. The problem I encounter is it will print the smallest updated_at value but the id that is printed with it may be associated with the other duplicate entry in some rows. It seems as though it is selecting the id in each grouping separately (and randomly) from the minimum updated_at value. How can I get the intended behavior of aquiring the id associated with the minimum updated_at value every time? Example Data: (id, mem_id, account, sub_account, updated_at) VALUES (2351, 12, 123456, 12345, 2016-04-30) (id, mem_id, account, sub_account, updated_at) VALUES (7638, 12, 123456, 12345, 2016-06-15) My objective here is to get the id of the updated_at value amongst these two that is the minimum value. Therefore I want id 2351 but in some rows, it will return the other id randomly.

Aucun commentaire:

Enregistrer un commentaire