How to select latest inserted row of items in a table

Question

I have a table current_rate that store current rate of different items with date and time. Here each time the current rate are inserted not updated so that it has historical records of rate variation of each items. **current_rate ** table

id	item_id	item_slug	rate	valid_date
1	2	wallet	400	2021-05-07 12:24:35
2	1	bag	1000	2021-05-07 11:13:32
3	1	bag	1100	2021-05-07 11:45:39
4	3	mug	200	2021-05-07 12:48:39
5	2	wallet	390	2021-05-07 12:13:39

I'm trying to get the last inserted row based on every item regardless of date and time. I've tried $currentStatus = CurrentRate::find()->groupBy(["item"])->orderBy(['id' => SORT_DESC]); and this will generate Rawquery SELECT * FROM current_rateGROUP BYitemORDER BYid DESC and didn't get the result. The Result which i want like

id	item_id	item_slug	rate	valid_date
3	1	bag	1100	2021-05-07 11:45:39
4	3	mug	200	2021-05-07 12:48:39
5	2	wallet	390	2021-05-07 12:13:39

How to get the result like the last inserted(id-autoincriment) of every item. Regards

How is this related to `jQuery` – Carsten Løvbo Andersen May 07 '21 at 06:38 — Carsten Løvbo Andersen, May 07 '21 at 06:38
Consider storing date and time as a single entity – Strawberry May 07 '21 at 06:41 — Strawberry, May 07 '21 at 06:41
i've change date and time as a single entity – code-droid May 07 '21 at 07:05 — code-droid, May 07 '21 at 07:05

score 0 · Accepted Answer · answered May 07 '21 at 07:26

Use GROUP BY along with SUB QUERY to get the final output.

Query SQL:

select * from current_rate
where valid_date in
(select MAX(valid_date) from current_rate group by item_id);

Schema SQL:

create table current_rate(
  id int NOT NULL AUTO_INCREMENT,
  item_id varchar(255),
  item_slug varchar(255),
  rate int,
  valid_date TIMESTAMP,
  PRIMARY KEY (id)
);

insert into current_rate (item_id, item_slug, rate, valid_date)
values (1, 'bag', 1000, '2021-05-07 11:13:32');
insert into current_rate (item_id, item_slug, rate, valid_date)
values (1, 'bag', 1100, '2021-05-07 11:45:39');
insert into current_rate (item_id, item_slug, rate, valid_date)
values (2, 'wallet', 400, '2021-05-07 12:24:35');
insert into current_rate (item_id, item_slug, rate, valid_date)
values (2, 'wallet', 390, '2021-05-07 12:13:39');
insert into current_rate (item_id, item_slug, rate, valid_date)
values (3, 'mug', 200, '2021-05-07 12:48:39');

Find the DB fiddle

Abhijeet Umbarkar · Answer 2 · 2021-05-07T06:59:48.843

-1

select * from `current_rate` where `item_id` = <item_id> AND `item_slug` = <item_slug> AND (`valid_date`, `time`) IN (select MAX(`valid_date`), MAX(`time`) from `current_rate` where `item_id` = <item_id> AND `item_slug` = <item_slug>)

You'll get single row every time for item id and item slug combination.

edited May 07 '21 at 06:59

answered May 07 '21 at 06:53

Abhijeet Umbarkar

51
9

How to select latest inserted row of items in a table

2 Answers2