CODEWITHSUNDEEP
mysql
sanjihan
sanjihan

Reputation: 6024

select most recently added record with value in MySQL

I have a table with the following structure

CREATE TABLE `data` (
  `type` varchar(64) DEFAULT NULL,
  `subscr_id` varchar(64) DEFAULT NULL
)

In this table, there are many records with subscr_id of id100. I would like to select a record with subscr_id of id100, that was added to the table most recently.

How can I do that?

Upvotes: 1

Views: 515

Answers (2)

cn0047
cn0047

Reputation: 17091

I think you have to improve your table design and add auto-inctemental primary key or created_at field. But if you can't do it or you need run query just once, you can try this approach (it's a bit tricky but it works 😉).

In general recent record will be present at the end of the table. For example we have table like this:

+------+-----------+
| type | subscr_id |
+------+-----------+
| a    | id100     |
| b    | id100     |
| c    | id100     |
| a    | id200     |
| b    | id200     |
| d    | id100     |
| c    | id200     |
| e    | id100     |
+------+-----------+

And here wee need calculate total count of interesting rows and use it for offset, like this:

set @offset = (select count(*) from data where subscr_id = 'id100') - 1;
set @sql = concat(
    "select * from data where subscr_id = 'id100' limit 1 offset ",
    @offset
);
prepare stmt1 from @sql;
execute stmt1;

The result will look like this:

+------+-----------+
| type | subscr_id |
+------+-----------+
| e    | id100     |
+------+-----------+

Upvotes: -1

Hieu Le
Hieu Le

Reputation: 1132

You add an ID - Indentify column. It's best performance in this/your situation.

ALTER TABLE data ADD COLUMN id INT NULL AUTO_INCREMENT FIRST, ADD KEY(id);

Run the below SQL, you will receive the record with subscr_id of id100, that was added to the table most recently most recently. 

SELECT * FROM `data` WHERE subscr_id = 'id100' ORDER BY id DESC LIMIT 1;

Upvotes: 2

Related Questions