Select unique rows based on 2 columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
h123z
Forum Newbie
Posts: 7
Joined: Tue Sep 18, 2007 5:18 am

Select unique rows based on 2 columns

Post by h123z »

Hi,
I have a table with 4 columns, I'd like to pull out only rows with a unique combination of column 2 and column 4.
For example:
table: col1, col2, col3, col4
row1: abc, 1, 11, def
row2: qrs, 1, 25, def
row3: xyz, 7, 2, xyz
row4: lmn, 6, 3, def


I would like to pull out ONLY rows 3 and 4, since they both have a unique combination of column 2 and column 4.
Is there any way i can do this?
I know i can use distinct to select unique rows based on the 2 columns, but that will also give me 1 of the 2 rows that have the same combo...

thank you!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Select unique rows based on 2 columns

Post by Eran »

Use the GROUP BY clause on columns 2 and 4.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Select unique rows based on 2 columns

Post by VladSun »

pytrin's query will still show at least one not unique combinations of col2 and col4 if there are any.
[sql]SELECT    mytable.*FROM     mytableGROUP BY      col2, col4HAVING      count(*) = 1 [/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Select unique rows based on 2 columns

Post by Eran »

True. Another alternative would be:

Code: Select all

 
SELECT
     mytable.*, CONCAT(mytable.col2,mytable.col4) AS unique_comb
 FROM
     mytable
 GROUP BY
      unique_comb
 
Post Reply