MIN annoyance

Anon

Trying to select the minimum post_id from the posts table in my database (to get the id of the first post in a topic) but I can't get the mysql query to work right.

I've got

$postidquery=mysql_query("SELECT MIN(post_id) FROM posts WHERE topic_id='$topic'",$db);

but I get no results...

If I run

$postidquery=mysql_query("SELECT post_id FROM posts WHERE topic_id='$topic'",$db);

I get no errors and I can get the largest by running it in a while loop and it finishes, then prints out the last value, but I need the first one!

Thanks for any help!

Anon

I can get it via a break statement, ending the code before it loops again, but seems like the wrong way to do it!

mithril

Try:

SELECT post_id FROM posts WHERE topic_id='$topic' ORDER BY post_id ASC LIMIT 0,1

This will return only one row, and it should be the lowest id number. If not, change ASC to DESC.... it's too late for my brain to work properly🙂

-geoff

[deleted]

Try this instead:
$postidquery=mysql_query("SELECT MIN(post_id) AS minpostid FROM posts WHERE topic_id='$topic'",$db);
$aRow = mysql_fetch_array($postidquery, MYSQL_ASSOC);
echo $aRow['minpostid'];

jc94062

Cheers!
And it is ASC, btw, do you actually need ASC, isn't that the default when nothing is put down?

Sorry for long delay in reply, I was ill and in no fit state to do anything, let alone code :-(

jc94062

Cheers here as well

The other code (Geoff) works, but what you gave is shorter, and I always prefer shorter code!

As I said to Geoff, sorry for the long delay in replying, I got ill and was in no fit state to do anything, let alone code :-(

mithril

Yes, ASC is the default ORDER BY sequence. However, I prefer to be as precise as possible in my code to aid in future readablity/debugging so I usually include it anyway🙂

-geoff

[deleted]

"but what you gave is shorter"

The advantage of what I gave over what Geoff gave is that my statement is the 'right' way :-)

Geoff's method means that the database must first sort all the records by id, and then take the first one in the list.
If you have 50M records, it will sort 50M records just to return one row.
My method just loops through the records looking for the lowest value, which is a lot easier for SQL to do.