SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `transaction_detail` ( `id` int NOT NULL, `trx_id` int NULL, `product_name` varchar(100) NULL, `quantity` int NULL, `price` int NULL ); CREATE TABLE `transactions`( `id` int not null primary key, `customer_id` varchar (100) not null, `order_id` varchar (100) not null, `transaction_date` timestamp not null, `status` varchar(50) NOT NULL, `vendor` varchar(50) ); INSERT INTO transactions(id, customer_id, order_id, transaction_date, status, vendor) values (1,'442818','TEST000001','2018-01-01 00:00:10','SHIPPED', 'Vendor A'), (2,'181820','TEST000001','2018-01-01 00:10:10','SHIPPED', 'Vendor A'), (3,'999019','TEST000001','2018-01-02 03:18:01','CANCELLED', 'Vendor A'), (4,'1923192','TEST000001','2018-02-04 05:00:00','CANCELLED', 'Vendor C'), (5,'645532','TEST000001','2018-02-10 16:00:10','SHIPPED', 'Vendor C'), (6,'1101011','TEST000001','2018-02-11 11:00:11','SHIPPED', 'Vendor C'), (7,'1020000','TEST000001','2018-02-10 00:00:00','SHIPPED', 'Vendor D'), (8,'40111234','TEST000001','2018-03-11 06:30:11','SHIPPED', 'Vendor D'), (9,'1923192','TEST000001','2018-03-12 10:00:11','CANCELLED', 'Vendor B'), (10,'1101011','TEST000001','2018-03-12 15:30:12','SHIPPED', 'Vendor B'), (11,'999019','TEST000001','2018-03-15 12:30:45','CANCELLED', 'Vendor A'), (12,'645532','TEST000001','2018-04-01 09:30:22','SHIPPED', 'Vendor A'), (13,'650013','TEST000001','2018-04-01 10:50:37','SHIPPED', 'Vendor C'), (14,'777734','TEST000001','2018-04-02 13:45:19','SHIPPED', 'Vendor D'); INSERT INTO transaction_detail(id, trx_id, product_name, quantity, price) values (1,1,'Beng beng',100,6000), (2,1,'Taro',80,5500), (3,2,'Beng beng',70,6000), (4,2,'Taro',41,5500), (5,2,'Indomie Kari Ayam',12,3000), (6,2,'Indomie Ayam Bawang',20,3100), (7,3,'Indomie Ayam Bawang',30,3200), (8,3,'Indomie Kari Ayam',90,3300), (9,3,'Taro',100,5500), (10,4,'Beng beng',40,6000), (11,5,'Teh Sariwangi Murni',50,8000), (12,6,'Indomie Kari Ayam',10,3000), (13,6,'Indomie Ayam Bawang',8,3100), (14,6,'Teh Sariwangi Murni',80,8000), (15,6,'Teh Hijau Cap Kepala Djenggot',15,9500), (16,7,'Coki-coki',70,1000), (17,8,'Bakmi Mewah',1500,13000); select * from( select *, row_number() over(partition by vendor order by transaction_date desc) as row_num from transactions) as v where v.row_num = 2