Page 1 of 1

Select unique rows based on 2 columns

Posted: Wed Jul 16, 2008 8:27 am
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!

Re: Select unique rows based on 2 columns

Posted: Wed Jul 16, 2008 9:00 am
by Eran
Use the GROUP BY clause on columns 2 and 4.

Re: Select unique rows based on 2 columns

Posted: Wed Jul 16, 2008 9:24 am
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]

Re: Select unique rows based on 2 columns

Posted: Wed Jul 16, 2008 9:43 am
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