CODEWITHSUNDEEP
phpmysqlselectunique
Fidelis
Fidelis

Reputation: 339

PHP/MySQL - select all rows where a field is unique

I have a table like this:

column_a  column_b
  foo        1
  bar        1
  bar        2
  baz        4

I'd like to have following result, containig all rows, that have a unique field value in column_b:

  bar        2
  baz        4

foo(0) and bar(1) both have the column_b value '1', so the '1' is not unique. Is there a MySQL-Expression that filters all rows that have a unique column_b?

Maybe something like that:

select * from table where isunique(column_b)

In Addition: I do not need something like DISTINCT!

edit (solved):

Thanks to YaK and jcho360, this one is the solution:

select column_a, column_b from table group by column_b having count(column_b) = 1

OR

select column_a, column_b, COUNT(1) AS total from table group by column_b having total = 1

(thx to andrewtweber)

Upvotes: 1

Views: 2622

Answers (3)

jcho360
jcho360

Reputation: 3759

Using the count function will provide a selection where the value in column_b only occurs once. Try this:

select * from table group by (column_b) having count(column_b) = 1;

Upvotes: 4

andrewtweber
andrewtweber

Reputation: 25509

SELECT column_a, column_b, COUNT(1) AS total
FROM table
GROUP BY column_b
HAVING total = 1

I believe that should work. This groups all column_b together and counts how many have that particular value, then limits the result set to those with a unique column_b value.

Upvotes: 3

Jamie
Jamie

Reputation: 189

Try select unique(column_b), column_a, from table instead? :)

Edit fix typo

Upvotes: -1

Related Questions