[mysql]get last/minimum row from query.

[madmenyo]

Hi,

 

I'm kinda stuck on a query. I have a table for messages with a timestamp field. Now i want to store a maximum amount of messages in that table, so each time a message is entered it must check the table if the num_rows > $maximum.

 

If that is true lookup the timestamp of the latest message of the last $maximum messages. Get that in a variable or array so i can delete everything older from the table.

 

Now i can de a query like this:

$lastmsg = mysql_query ("
		SELECT time
		FROM chat
		ORDER BY time DESC	
		LIMIT $store_num
") or die (mysql_error());

 

And loop through the query, check the oldest timestamp each loop and if it's older then put that into a variable. But obviously i already know it's the last table . I would love to do something like this:

$lastmsg = mysql_query ("
		SELECT LAST(time)
		FROM chat
		ORDER BY time DESC 
		LIMIT $store_num <---- this needs priority
") or die (mysql_error());

 

But like SELECT MIN(time) it would just select the minimum in the whole table and then LIMIT becomes obviously useless unless there are more rows with the same minimum in the entire table.

 

So can i build a query to select 1 row out of many rows and how to do that?

 

tx!

[Mchl]

SELECT time FROM chat ORDER BY time DESC LIMIT $store_num, 1