SQLize Online / PHPize Online  /  SQLtest Online

create table orders ( ID serial, STORE_ID varchar(32), UPDATED_AT timestamp ); insert into orders (ID, STORE_ID, UPDATED_AT) values (1, 'store-1', '2021-01-01'), (2, 'store-2', '2021-01-02'), (3, 'store-3', '2021-01-03'), (4, 'store-1', '2021-01-04'), (5, 'store-2', '2021-01-05'), (6, 'store-3', '2021-01-06'), (4, 'store-1', '2021-01-04'), (5, 'store-4', '2021-01-05'), (6, 'store-3', '2021-01-06'), (4, 'store-4', '2021-01-04'), (5, 'store-2', '2021-01-05'), (6, 'store-3', '2021-01-06'); select orders.* , row_number() over (partition by STORE_ID order by UPDATED_AT desc) rn_order, dense_rank() over(order by STORE_ID) store from orders order by STORE_ID;