CODEWITHSUNDEEP
mysqlsql
KazikM
KazikM

Reputation: 1349

MySQL: Select rows where field contains unique value

In MySQL database, I have a products table containing various product data, including company.

id name company
1 Product 1 AAA
2 Another product BBB
3 One more product CCC
4 Item ZZZ BBB
5 Product A AAA
6 Product uT DDD
7 Product 2z AAA

Now I want to build a query which will select all products which have a unique company - I mean a company, which appears only once in the whole table. In the above example - row 3 with company CCC and row 6 with company DDD.

How can I achieve this?

Upvotes: 1

Views: 58

Answers (3)

gview
gview

Reputation: 15401

You can use GROUP BY ... HAVING clause to find solo companies, then join to them by the company name.

SELECT * FROM products p
JOIN 
(SELECT company FROM products
GROUP BY company
HAVING COUNT(*) = 1) pg
ON p.company = pg.company

SQLFiddle here

MySQL will actually let you do this even simpler query. This works in this circumstance because there is only one row in the group you are looking for. If however, you were looking for all the products for companies that have 2 products, the simpler version won't work anymore, as you'll only get one row back. The original query would continue to work with other HAVING clauses like HAVING COUNT(*) > 10

SELECT id, product, company
FROM products
GROUP BY company
HAVING COUNT(*) = 1

Upvotes: 1

Hexydec
Hexydec

Reputation: 21

You need to group by company and then only select the rows that have one company:

SELECT `id`, `name`, `company` FROM `products` GROUP BY `company` HAVING COUNT(*)=1;

Upvotes: 0

Zakaria
Zakaria

Reputation: 4796

You can use count(id) over(partition by company)

select id, name, company from
(select id, name, company , count(id) over(partition by company) cnt
from products) t
where cnt = 1;

Fiddle

Upvotes: 1

Related Questions