query doesnt like same values in a field

[spyke01]

im doing a rma database system for my job, but im having some problems in the dev phase, the query im using is:

$sql = "SELECT * FROM `rmas` GROUP BY rmas_urgency ORDER BY rmas_date DESC LIMIT $limit1, $limit2";            
$result = mysql_query($sql);

i add new rmas by doing this:

$sql = "INSERT INTO `rmas` (rmas_title,rmas_date,rmas_tech) VALUES ('NEW RMA -- OPEN TO CHANGE','" . time() . "','HG TECH');";        
$result = mysql_query($sql);

 

the database export is:

CREATE TABLE `rmas` (
  `rmas_id` mediumint(8) NOT NULL auto_increment,
  `rmas_title` varchar(250) NOT NULL default '',
  `rmas_user_id` mediumint(8) NOT NULL default '0',
  `rmas_status` tinyint(1) NOT NULL default '1',
  `rmas_date` int(11) NOT NULL default '0',
  `rmas_tech` varchar(50) NOT NULL default '',
  `rmas_fixable` tinyint(1) NOT NULL default '0',
  `rmas_action_required` text NOT NULL,
  `rmas_action_status` tinyint(1) NOT NULL default '1',
  `rmas_repair_man` varchar(100) NOT NULL default '',
  `rmas_repair_date_sent` int(11) NOT NULL default '0',
  `rmas_repair_track_no` varchar(100) NOT NULL default '',
  `rmas_in_stock` tinyint(1) NOT NULL default '0',
  `rmas_replaced_date` int(11) NOT NULL default '0',
  `rmas_replaced_tech` varchar(50) NOT NULL default '',
  `rmas_replaced_return_track_no` varchar(100) NOT NULL default '',
  `rmas_replaced_dist` varchar(100) NOT NULL default '',
  `rmas_replaced_date_ordered` int(11) NOT NULL default '0',
  `rmas_replaced_order_track_no` varchar(100) NOT NULL default '',
  `rmas_urgency` mediumint(8) NOT NULL default '0',
  PRIMARY KEY  (`rmas_id`)
) TYPE=MyISAM AUTO_INCREMENT=13;

INSERT INTO `rmas` VALUES (10, 'NEW RMA -- OPEN TO CHANGE', 0, 1, 1136575106, 'HG TECH', 0, '', 1, '', 0, '', 0, 0, '', '', '', 0, '', 1);
INSERT INTO `rmas` VALUES (11, 'NEW RMA 11-- OPEN TO CHANGE', 0, 1, 1136575107, 'HG TECH11', 0, '', 1, '', 0, '', 0, 0, '', '', '', 0, '', 11);
INSERT INTO `rmas` VALUES (12, 'NEW RMA -- OPEN TO CHANGE', 0, 1, 1136575109, 'HG TECH', 0, '', 1, '', 0, '', 0, 0, '', '', '', 0, '', 1);

 

ok now onto the problem. if the rmas_urgency field is the same then only the oldest row will be outputted, this is a problem becuase ineed the database to be treated as a point system, ie. date is most important and then a point scale from 1-5 5 is the most urgent

 

does anyone know whats going on here?

[fenway]

The problem is that once you group by the urgency, you no longer "have" each of the dates on which to ORDER BY (i.e. there is now only one record per urgency value!). You'll need to use a subquery (4.1+) to find all of the max dates, and then do the urgency grouping after.

 

Try the following:

 

SELECT * FROM `rmas` WHERE rmas_date IN ( SELECT MAX(rmas_date) FROM `rmas` GROUP BY rmas_urgency ) GROUP BY rmas_urgency;

 

Hope that helps.

[spyke01]

heres the error i get upon running the query

SQL query: Documentation

SELECT *
FROM `rmas`
WHERE rmas_date
IN (

SELECT MAX( rmas_date )
FROM `rmas`
GROUP BY rmas_urgency
)
GROUP BY rmas_urgency
LIMIT 0 , 50

MySQL said: Documentation
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(rmas_date) FROM `rmas` GROUP BY rmas_urgency ) GROUP 

[fenway]

As I indicated above, subqueries are only supported in MySQL 4.1+ -- which version of the server are you using? Your error message suggests a lower version, in which case you're stuck taking the inner query, dumping the results to a temporary table, and then running the outer query on the temporary table records, and dropping the temp table when you're done.

 

Hope that helps.