I was recently presented with the need to select only values that are unique. Shortly after finishing I came across someone asking how to pick off unique entries using SQL. The task is complicated when you consider it in terms of logic but SQL provides the tools to make it surprisingly simple.

SELECT `column`
FROM `table`
GROUP BY `column`
HAVING (
COUNT( `column`) =1
) 

Let's say you were using the test data below:

CREATE TABLE `ground` (
  `ground_id` int(6) NOT NULL default '0',
  `material` varchar(255) default NULL,
  `hard` decimal(10,2) default '0.00',
  PRIMARY KEY  (`ground_id`)
);

INSERT INTO `ground` 
(`ground_id`, `material`, `hard`) 
VALUES
(0, 'soil', 25.00),
(2, 'gravel', 75.00),
(3, 'asphalt', 75.00);

You can query for all types of ground having unique hardness values with the following query:

SELECT `material` FROM `ground` 
GROUP BY `hard` HAVING ( COUNT( `hard`) =1 ) 

This SQL query returns the value soil which is the only row in the database with a unique value for the field hard.