using MIN

[cleary1981]

Hi,

 

I am trying to use the MIN function in my script to get the record that has the minimum value for xpos and ypos. The problem i am having is that when I add the min function in the result is blank.

 

Heres my attempt

$query = "SELECT object_name, MIN(xpos), MIN(ypos) FROM object WHERE proj_id = '78' GROUP BY object_name";
  		$result = mysql_query($query) or die(mysql_error());
  		$left = mysql_fetch_assoc($result);
  		echo "xpos ".$left['xpos'];
  		echo "ypos ".$left['ypos'];

[The Little Guy]

try:

 

$query = "SELECT object_name, MIN(xpos) as xpos, MIN(ypos) as ypos FROM object WHERE proj_id = '78' GROUP BY object_name";
  		$result = mysql_query($query) or die(mysql_error());
  		$left = mysql_fetch_assoc($result);
  		echo "xpos ".$left['xpos'];
  		echo "ypos ".$left['ypos'];

[cleary1981]

That worked. thanks

[cleary1981]

thought that had worked for me. Problem I have now is that i am trying to get the one record with the lowest of two values but instead it returns me the lowest value in each case from different records. Heres my code

 

//left= object with lowest x and y values
  		$query = "SELECT object_name, MIN(xpos) as xpos, MIN(ypos) as ypos FROM object WHERE proj_id = '78' GROUP BY proj_id";
  		$result = mysql_query($query) or die(mysql_error());
  		$left = mysql_fetch_assoc($result);
  		// echo "proj_id ".$left['proj_id'];
  		echo "xpos ".$left['xpos'];
  		echo "ypos ".$left['ypos'];

[JonnoTheDev]

That is because your query is writen to do that. MIN is not the way to do this because it will return the minimum values for any records that have a proj_id of 78. You should use a sort query and then limit it to 1 record

 

SELECT object_name, xpos, ypos, from object WHERE proj_id='78' ORDER BY xpos,ypos ASC LIMIT 1

[cleary1981]

Wat if I wanted to get MAX(xpos) MIN (ypos)?

[JonnoTheDev]

Then I would use a temporary table

[cleary1981]

Wats a temporary table? How do I do that?

[JonnoTheDev]

A temporary table is a table created from the results of a query or a set of queries (nested possibly). They are useful when you cannot return the results you require from a single query on current tables.

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

 

However you should be able to return the results you require by altering the sort order on the xpos and ypos fields so:

 

SELECT object_name, xpos, ypos, from object WHERE proj_id='78' ORDER BY xpos ASC ,ypos DESC LIMIT 1

SELECT object_name, xpos, ypos, from object WHERE proj_id='78' ORDER BY xpos DESC ,ypos ASC LIMIT 1