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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lovelf
Forum Contributor
Posts: 153
Joined: Wed Nov 05, 2008 12:06 am

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

Post 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.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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
Post Reply