Select the highest id

[bobnew32]

Does anyone know any code that would select the highest id in a table called Ratings, and also to select the highest 3 ids? Thx, I'm using it to put the highest one code on the main page, and on the other page i'm gonna use the highest 3 id code.

[brotherhood of LAN]

deleted, off to load up mysql :P.....assuming you are using mysql...

//edited
SELECT * FROM ratings ORDER BY id DESC LIMIT 3

[edited by: brotherhood_of_LAN at 9:41 pm (utc) on May 4, 2003]

[bobnew32]

So that code is saying look through everything and select the 3 highest ids and order them by id number? Would the code for the highest id number (just for one) be

Select * FROM ratings order by id limit 1. What were you saying about sorting them first? If you do do it this way wouldnt it be a strain on server?

[dmorison]

Hi,

You can do it in the select statement using

"SELECT * FROM table ORDER BY id DESC LIMIT x"

where x is the 1 for the higest, 3, for the top 3 etc.

[brotherhood of LAN]

>What were you saying about sorting them first? If you do do it this way wouldnt it be a strain on server?

Well I don't know the intricates of mySQL, but I'd think you'd be better off having it sorted because it would require less criteria to get a match.

I guess it depends how your inserts work and what you intend to do with the ratings table. You could sort the table every 24 hours and (hopefully) it wouldnt be much of a strain as a query that has to order the id's for every request for the highest ID's in an unsorted order.

If you sorted the ratingID in descending order the query would be

SELECT id FROM ratings LIMIT 1, which should be quite quick!

>Select * FROM ratings order by id DESC limit 1

yep thaat would get the highest ID in the table, as would this

SELECT max(id) FROM ratings

[lorax]

>> If you do do it this way wouldnt it be a strain on server?

How many records are we talking here and how much traffic?

[BCMG_Scott]

Assuming you are using the highest and 3 highest ids in the same script then:

select id from ratings order by id desc limit 3;

would return the 3 highest and the highest. The highest is, of course, the first row returned. So when you fetch your rows, row 1 will be your highest id.

If you are running seperate scripts then:

select max(id) from rating;

is your best bet.

As far as load/performance - these are very low threshold queries, you would hit your max connection threshold before you would have memory or buffer issues.

Also, you might want to run an explain on the first query to see if you need any indexes. I suspect that you may already have an index on your id column:

explain select id from rating order by id desc limit 3;

[mysql.com...]

Scott

[bobnew32]

You talk about having the highest id as being row 1, how do I reference each row/put them in an array to reference them? Of course I would want to reference row 1, 2, and 3, but I could also put this knowledge to other purposes.

[lorax]

Assuming you use:

$q = "SELECT id
FROM ratings
ORDER BY id DESC LIMIT 3";

Your next line of code would be:

$r = mysql_query($q);

To call the ids you use a loop to iterate through the array because the frist time through the internal pointer of the array is at position 0. To use that item you simply call position 0 or call it by it's associative name 'id' like so:

echo $r_array['id']

OR

echo $r_array[0]

To get each one of the ids add the loop function like so

for ($i=0; $i<3; $i++) {
$r_array = mysql_fetch_array($r);
echo $r_array['id']."<br />";
}

$r_array is the array and the first item in that array is the highest id (as dictacted by the ORDER BY line of the query). By looping and calling mysql_fetch_array you index the array to the next position and then you can use the value or values as you see fit.