How in MySQL I get auto-increment field value of last record ?

[leonidassavvides]

How in MySQL I get auto-increment field value of last record ? what if I delete a record and I want to get field's (auto-increment) value of last record ? It will still counts the autoincrement from the point left even if I delete records or the last record ? how I count all records currently in DB Table ?

[JasonDFR]

To get the last auto-incremented value:


SELECT `field`
FROM `table`
ORDER BY `field` DESC
LIMIT 1 ;

Where `field` is auto_incremented.

Deleting records will not affect how this works. Don't worry about it.

To count all records:


SELECT COUNT( `field` )
FROM `table`;

[leonidassavvides]

by
"LIMIT 1"
we get first record ?

[JasonDFR]

ORDER BY `field` DESC
LIMIT 1 ;

Will give the last record.

If you have:

1
2
3
4
5

And you ORDER BY `` DESC (descending), the order is:

5
4
3
2
1

And when you LIMIT 1, you are only returning the first record. In the above case, it would return record number 5.

Got it?

J

[quakeglen]

there is a problem when you try to get the last record using:

ORDER BY id DESC

if you delete a record inside there is no problem... you get the last one... but if you delete the last one and then add a new register, the auto_increment value will not be the same as las id in table...

so, how could we get it in both cases?

[JasonDFR]

Before:

5
4
3
2
1

Delete 5

Add a record

After:

6
4
3
2
1

Order by id desc limit one returns 6, being the last record inserted.

Unless I misunderstood you, there is no difference.

[XManBG]

Hi,

just wanted to bother an old theme: reading the last value from MySQL table. The one solution is nice: sort the whole table backwards and take the first value. But how about performance?? How about tables with 1 000 000 values... Does someone already tested the access speed of that??

Cheers

[djr33]

ORDER BY `category` DESC
That will order the results by `category` in descending order (such as 9 to 1 or Z to A). There's really no need to overthink any of this. MySQL doesn't look through all of the data and reorganize it. It just grabs the match that works best within that single column. Searching is what databases are designed for and that sort of operation will not be too difficult. Of course if you have millions of entries any operation may be difficult. But you won't find a more efficient way. Of course you could actually maintain the database in an ordered way and just display the first result, but that would be a lot of work and probably not more efficient overall. And I don't know if that would actually be faster or if it would still reorganize it to give you the standard order.

Of course if you do something more complicated like substring searching (finding a match that contains a certain bit of text) then that will be more difficult, but you'll probably be surprised at how efficient MySQL still is.

[XManBG]

:) While I was waiting for an answer, I've started a simple test to see, how this finding works... Already have 190 000 rows and the searching of the last index takes not much than some milliseconds. Nice work this MySQL...