2

I have a table called Live, and in the table I have public_id. In public_id are numbers (e.g. 1,2,3,4,5). How can I get the highest number or the last entry's number?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
John Doe
  • 3,559
  • 15
  • 62
  • 111

4 Answers4

9

I'd choose 

SELECT public_id FROM Live ORDER BY public_id DESC LIMIT 1;

It has already been posted, but i'll add something more:

If you perform this query usually you can create an index with inverted ordering.

Something like:

CREATE INDEX ON Live (public_id DESC)

Please Note

DESC

PHP + MySQL code:

<?php
$result = mysql_query('SELECT public_id FROM Live ORDER BY public_id DESC LIMIT 1;');
if (mysql_num_rows($result) > 0) {
   $max_public_id = mysql_fetch_row($result);
   echo $max_public_id[0]; //Here it is
}
?>
santiagobasulto
  • 11,320
  • 11
  • 64
  • 88
  • All this prints out is `array`, how can I get the individual number like 4? – John Doe Sep 06 '11 at 03:59
  • Sorry, my mistake. I edited it. BTW. You should try to get in the insights of the language. You can print array content with print_r() function. Take a look at php.net – santiagobasulto Sep 06 '11 at 04:02
  • 1
    I did know about `print_r();` but didn't think that would resolve the problem. This worked, thanks for your help! – John Doe Sep 09 '11 at 21:47
5
SELECT public_id FROM Live ORDER BY public_id DESC LIMIT 1;

would give you the highest number

Edit: For the php 

$conn = mysql_connect(....
$query = "SELECT public_id FROM Live ORDER BY public_id DESC LIMIT 1";
$result = mysql_query($query, $conn);
Mobius
  • 699
  • 4
  • 11
3

Another option is to use MAX, ie

SELECT MAX(public_id) FROM Live
ain
  • 22,394
  • 3
  • 54
  • 74
  • 1
    It's still a valid answer to the question. Is it really slower depends on data... – ain Sep 05 '11 at 22:41
  • 1
    I didn't downvote, I just added the information as the performance issue can be very important. – FrankS Sep 05 '11 at 22:42
  • 1
    @FrankS: In the link you provided, the answer states that `MIN()` is actually (sliightly) **faster** than `ORDER BY ... LIMIT 1`. – ypercubeᵀᴹ Sep 05 '11 at 22:50
  • This answer is 100% correct. It will use the index (if there is one!) of `public_id`. The other way (`ORDER BY ... LIMIT`) has the advantage that it can return the whole row with maximum id - with `SELECT *` - and also more than one rows - with `LIMIT x` . – ypercubeᵀᴹ Sep 05 '11 at 22:55
  • My fault, so I deleted the old comment. Here is the [link](http://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit) to the answer I mentioned. – FrankS Sep 06 '11 at 07:01
0

You can either use ORDER BY public_id DESC LIMIT 1; to obtain the record with the highest id (I guess you are using autoincrement attribute on public_id) or MySQL MAX() function like this:

SELECT * FROM table WHERE public_id = MAX(public_id);

edit:

Here is how you will do it in php:

// executing the QUERY request to MySQL server, mysql_query() returns resource ID
$result = mysql_query('SELECT public_id FROM Live ORDER BY public_id DESC LIMIT 1');
// then we use this resource ID to fetch the actual value MySQL have returned
$row = mysql_fetch_array($result, MYSQL_FETCH_ASSOC);
// $row variable now holds all the data we got from MySQL, its an array, so we just output the public_id column value to the screen
echo $row['public_id']; // you can use var_dump($row); to see the whole content of $row
ludesign
  • 1,353
  • 7
  • 12
  • So how would you get the number? Sorry this may sound like a stupid question but I'm still new to MySQL. Would you say $last_number = SELECT public_id FROM Live ORDER BY public_id DESC LIMIT 1;? – John Doe Sep 05 '11 at 22:37
  • As the query tells MySQL to return only one record and this is the record having the highest public_id number it will contain that value (the highest number). – ludesign Sep 05 '11 at 22:50