select id < MAX(id)

[delfa]

I need some help here!

Can you tell me how do I select from mysql table all the rows except the latest 5.

That´s all!

thanx !

[johanvdw]

2 possibilities
this first possibility which you seemed to suggest only works when you have all id's. When you delete one of the latest 5 it will give problems. Eg if your id's are 1,2,3,4,6,7 it will show until 2, but you want it to show until 1.
Select * from table where id < (max(id)-5);

The better code (it always works well) is:
Select * from table limit 0, (count(id)-5) ORDER BY id ASC;

You select all records, ordered by id, and you select all but the latest 5.

[MrAlaska]

My version of MySql breaks when I try to run the statements suggested by johanvw. Maybe I am brain-dead, but I could not come up with a way to do this with MySql with a single SQL statement, I had to do it with two sql statements.

The code I used is not very elegant and there is no doubt plenty of room for improvement, but I figured as long as it worked anyway I would toss it up for discussion, improvement, whatever. I would certainly be interested knowing a cleanerr way, but if someone comes up with one I will prolly be slapping my forehead and going, "OF COURSE!"

This should work as a stand alone PHP page by changing the variables at the top of the page:

<?php
//(example only, but the syntax has been tested)
$table="table"; /** name of your table **/
$table_id="id"; /** name of your table index **/
$mysql_host = "localhost";/** your local path **/
$mysql_user = "root";	/** your mysql user name **/
$mysql_pass = "";    /** your mysql user password **/
$mysql_base = "mysql";/** name of mysql database **/

$mysql_link = mysql_connect
	($mysql_host,$mysql_user,$mysql_pass)
    or die (mysql_errno().": ".mysql_error());
mysql_select_db($mysql_base)
    or die ("could not connect to db<p>".
    mysql_errno().": ".mysql_error());
//first sql query collects highest number to view
$sql="select $table_id
from $table
order by $table_id desc
limit 6
";
$result=mysql_query($sql) 
	or die ("<p>$sql<p>".mysql_error());
$i=0;
while($row=mysql_fetch_array($result, MYSQL_BOTH)){
	$i++;
	if ($i==6) $max_row = $row[$table_id];
}

//second sql statement collects all but most recent 5
$sql="select *
from $table
where $table_id <= $max_row
order by $table_id desc
";
$result=mysql_query($sql) 
	or die ("<p>$sql<p>".mysql_error());

//now display all the results
$fields=mysql_num_fields($result);
echo "<table><tr>";
for ( $i=0; $i < $fields; $i++){
	echo "<th>";
	echo mysql_field_name($result, $i);
	echo "</th>";
}
echo "</tr>";
while($row=mysql_fetch_array($result, MYSQL_BOTH)){
	echo "<tr>";
	for ( $i=0; $i < $fields; $i++){
		echo "<td>$row[$i]</td>";
	}
	echo "</tr>";
}
echo "</table>";
?>

[delfa]

Hi johanvdw!

I´tried to use the second possibility.

But it simply doesn´t work.

It gives me this error:

1064: You have an error in your SQL syntax near '(count(id)-5) ORDER BY id ASC' at line 1

did I forgot something? 😕

thanx!

[johanvdw]

Originally posted by delfa
Hi johanvdw!

I´tried to use the second possibility.

But it simply doesn´t work.

It gives me this error:

1064: You have an error in your SQL syntax near '(count(id)-5) ORDER BY id ASC' at line 1

did I forgot something? 😕

thanx!

I've been trying to find out why it doesn't work. Seems like you have to use count in a different query. Strange, because I remember using this code (maybe that was postgresql).

This one should work
$result=mysql_query("Select count() from table;");
//counts how many entries in the table
//(BTW mr alaska, I think this query takes a lot less time than yours)
while($row=mysql_fetch_array($result))
{$count=$row[0] -5 ;}
$result2=mysql_query("Select from table limit $count ORDER BY id ASC;");

[MrAlaska]

Originally posted by johanvdw
$result=mysql_query("Select count() from table;");
//counts how many entries in the table
//(BTW mr alaska, I think this query takes a lot less time than yours)
while($row=mysql_fetch_array($result))
{$count=$row[0] -5 ;}
$result2=mysql_query("Select from table limit $count ORDER BY id ASC;");

I agree that would be much faster, your way I believe could even trim it up a bit more by getting rid of the while loop.

$result=mysql_query("Select count() from $table;");
$row = mysql_fetch_row($result);
$count = $row[0]-5;
$result=mysql_query("Select from $table order by $table_id asc limit $count;");
//this code tested functional replacing my original SQL

I structured mine the way I did to leave the flexibilty to easily sort descending or ascending depending on what the user wanted. The fact that I am looping through five records that I know I don't need anything from seems to indicate a performance problem, though.

As far as using aggregates, it breaks SQL to use them anywhere but a select clause or a having clause, at least from what I have seen. I thought your second option had some potential, but I could never get it to work. Even on a fully functional database the only time you can use them in a where clause is in a subselect, which is what you may have been thinking of.

There must be some way to sort the fields in the having clause to do this project in one query, but I'll be switched if I can think of a way.

[delfa]

Thanx johanvdw!
Thanks MrAlaska!

Both codes workes very well.

I was almost thinking about forget about ID´s and sort by time and date or something else.

Thanks again!