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

Question

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)

@Nikolai Wüstemann: No problem, but I wouldn't mind a +1 instead ;) — RandomSeed
– RandomSeed, Commented Jun 13, 2012 at 18:21

octopusgrabbus · Accepted Answer · 2012-06-13 19:37:15Z

4

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;

edited Jun 13, 2012 at 19:37

octopusgrabbus

10.7k15 gold badges75 silver badges137 bronze badges

answered Jun 13, 2012 at 17:43

jcho360

3,7491 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RandomSeed Over a year ago

HAVING COUNT(column_b) = 1 should suffice, because if there is no row, then it is not included in the result at all. Even worse if your column_b contains NULL values: then their COUNT() equals 0, and will be returned (with a "random" value as column_a value)

andrewtweber · Accepted Answer · 2012-06-13 17:47:08Z

3

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.

answered Jun 13, 2012 at 17:47

andrewtweber

25.8k22 gold badges93 silver badges112 bronze badges

Comments

Jamie · Accepted Answer · 2012-06-13 17:43:50Z

-1

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

Edit fix typo

answered Jun 13, 2012 at 17:43

Jamie

1896 bronze badges

2 Comments

RandomSeed Over a year ago

-1: in which version did they introduce the UNIQUE() function?

Jamie Over a year ago

Whoops my bad, was thinking Oracle syntax where unique works as distinct :(

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related