Jump to content


Photo

query doesnt like same values in a field


  • Please log in to reply
3 replies to this topic

#1 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 06 January 2006 - 07:29 PM

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?

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 January 2006 - 07:54 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 07 January 2006 - 02:20 PM

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 


[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 January 2006 - 06:44 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users