CODEWITHSUNDEEP
mysqlsqlfunctionaggregate
Stanton
Stanton

Reputation: 1374

SQL - Find max based on contents of another column

There has to be an elegant way to do this, but I'm stuck.

I have a table with chemistry data. Some values are 'equal to' and some are 'less than' the reported value, and this is denoted in an adjacent column (with '<' or '='). I've inserted an example below.

I need a query that will return the max value for each element that's associated with an '=' if there is one, even if there are higher values associated with '<'. Or if the highest value is associated with '=', or if all values are '<', just return the highest value. In the example below, I'd need to return Copper = 10, Nickel = 10, but Lead = 9.

Seems simple, but I just can't work it out. Any help is, as always, much appreciated.

------------------------------
-- Table structure for `chem`
------------------------------
DROP TABLE IF EXISTS `chem`;
CREATE TABLE `chem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Element` varchar(12) DEFAULT NULL,
`Value` tinyint(4) DEFAULT NULL,
`Qualifier` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of chem
-- ----------------------------
INSERT INTO `Chem` VALUES ('2', 'Copper', '1', '=');
INSERT INTO `Chem` VALUES ('3', 'Copper', '1', '=');
INSERT INTO `Chem` VALUES ('4', 'Copper', '3', '=');
INSERT INTO `Chem` VALUES ('5', 'Copper', '4', '=');
INSERT INTO `Chem` VALUES ('6', 'Copper', '5', '=');
INSERT INTO `Chem` VALUES ('7', 'Copper', '6', '=');
INSERT INTO `Chem` VALUES ('8', 'Copper', '7', '=');
INSERT INTO `Chem` VALUES ('9', 'Copper', '8', '=');
INSERT INTO `Chem` VALUES ('10', 'Copper', '9', '=');
INSERT INTO `Chem` VALUES ('11', 'Copper', '10', '=');
INSERT INTO `Chem` VALUES ('12', 'Nickel', '1', '<');
INSERT INTO `Chem` VALUES ('13', 'Nickel', '2', '<');
INSERT INTO `Chem` VALUES ('14', 'Nickel', '3', '=');
INSERT INTO `Chem` VALUES ('15', 'Nickel', '4', '<');
INSERT INTO `Chem` VALUES ('16', 'Nickel', '5', '=');
INSERT INTO `Chem` VALUES ('17', 'Nickel', '6', '=');
INSERT INTO `Chem` VALUES ('18', 'Nickel', '7', '=');
INSERT INTO `Chem` VALUES ('19', 'Nickel', '8', '=');
INSERT INTO `Chem` VALUES ('20', 'Nickel', '9', '=');
INSERT INTO `Chem` VALUES ('21', 'Nickel', '10', '=');
INSERT INTO `Chem` VALUES ('22', 'Lead', '1', '<');
INSERT INTO `Chem` VALUES ('23', 'Lead', '2', '<');
INSERT INTO `Chem` VALUES ('24', 'Lead', '3', '=');
INSERT INTO `Chem` VALUES ('25', 'Lead', '4', '=');
INSERT INTO `Chem` VALUES ('26', 'Lead', '5', '=');
INSERT INTO `Chem` VALUES ('27', 'Lead', '6', '<');
INSERT INTO `Chem` VALUES ('28', 'Lead', '7', '=');
INSERT INTO `Chem` VALUES ('29', 'Lead', '8', '=');
INSERT INTO `Chem` VALUES ('30', 'Lead', '9', '=');
INSERT INTO `Chem` VALUES ('31', 'Lead', '10', '<');

Upvotes: 2

Views: 1508

Answers (2)

Jeff Wu
Jeff Wu

Reputation: 2578

Another solution is to use a CASE statement within your MAX statement. This is a common way to do SUMIF, COUNTIF, etc. type statements in SQL. This trick extends will to many situations where you want to filter the value that you're aggregating.

This solution is very efficient because it only requires a single scan of the dataset, unlike solutions with a join or a union.

For example:

SELECT Element
       ,COALESCE(MAX(CASE WHEN Qualifier = '=' THEN Value ELSE NULL END), MAX(CASE WHEN Qualifier = '<' THEN Value ELSE NULL END))
FROM chem
GROUP BY Element

Upvotes: 3

Larry Lustig
Larry Lustig

Reputation: 50990

 SELECT Element, MAX(Value) FROM Chem WHERE Qualifier = '=' GROUP BY Element
 UNION ALL
 SELECT Element, MAX(Value) FROM Chem C1 WHERE
   NOT EXISTS (SELECT * FROM Chem WHERE Element = C1.Element AND Qualifier = '=')
   GROUP BY Element

This performs two queries and assembles the results into a single result set.

The first query finds the maximum Value for each Element that has an "=" qualifier. The second query handles the other case, in which there's no "=" qualifier, and returns the maximum Value for each of this set of Elements.

Upvotes: 1

Related Questions