select last inserted row based on date

Question

Main Problem Is:- select last inserted row based on date

i want to be able to select distinct ref row with the last created_At date.

this is my table and data

DROP TABLE IF EXISTS `transactions_logs`;
CREATE TABLE IF NOT EXISTS `transactions_logs` (
  `trans_log_Id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `etat_de_commande` varchar(100) NOT NULL,
  `ref` varchar(10) NOT NULL,
  `commentaire` text NOT NULL,
  `staffId` bigint(20) UNSIGNED NOT NULL,
  `Created_At` datetime NOT NULL,
  PRIMARY KEY (`trans_log_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO `transactions_logs` (`trans_log_Id`, `etat_de_commande`, `ref`, `commentaire`, `staffId`, `Created_At`) VALUES
(1, 'waiting confirmation', '429735061', '', 1, '2020-11-09 12:11:43'),
(2, 'waiting confirmation', '472143970', '', 1, '2020-11-09 13:45:57'),
(3, 'confirmed', '429735061', '', 1, '2020-11-09 13:46:12'),
(4, 'ready', '429735061', '', 1, '2020-11-09 13:46:18'),
(5, 'picked', '429735061', '', 1, '2020-11-09 14:46:25');
COMMIT;

I want to be able to get this result

(2,'waiting confirmation','472143970',1,'2020-11-09 13:45:57'),
(5,'picked','429735061',1,'2020-11-09 14:46:25')

Answer 1

One option uses window functions, available in MySQL 8.0:

select *
from (
    select t.*, 
        rank() over(partition by ref order by created_at desc) rn
    from transactions_logs t
) t
where rn = 1

You can also use a correalted subquery for filtering - this works in all MySQL versions:

select t.*
from transactions_logs t
where t.created_at = (
    select max(t1.created_at)
    from transactions_logs t1
    where t1.ref = t.ref
)

The latter would take advantage of an index on (ref, created_at).