Page 1 of 1

select * duplicates, then select * non-duplicates - mysql

Posted: Sat Oct 27, 2012 10:24 am
by lovelf
The question is for the example table structure:

mytable

Code: Select all

fielda, fieldb
val1    valx
val2    valy
val3    valz
val1    vala

I want to select from mytable with a query:

Code: Select all

fielda, fieldb
val1    valx
val1    vala

basically select * from table where fielda is duplicated.

then with a different query select from mytable

Code: Select all

fielda, fieldb
val2    valy
val3    valz

val1 on fielda was encountered as a duplicate therefore it wasn't selected and the rest were.

Re: select * duplicates, then select * non-duplicates - mysq

Posted: Sun Oct 28, 2012 11:22 am
by McInfo
Rows where x.a is not unique:

Code: Select all

SELECT x.a, x.b FROM (SELECT a FROM x GROUP BY a HAVING COUNT(a) > 1) y JOIN x ON x.a = y.a
Rows where x.a is unique:

Code: Select all

SELECT a, b FROM x GROUP BY a HAVING COUNT(a) = 1