February 5th, 2012, 08:09 PM
Distinct on multiple fields
Select all columns from a table except primary key such that the columns do not repeat all at the same time.
I have a table 'testTable' such as:
Col1 is the key. I want to SELECT all the columns provided that Col2, Col3 and Col4 do not repeat.
One or two of them can repeat at a time. But All of them should not ever repeat.
In case of above table the query should return only row 1,2 and 4. It shouldn't return row 3 because in row three all the three columns are repeating (matching with row 1).
I have tried using Distinct and Order By. But they either take 1 column or all the columns.
Any help or suggestion would be highly appreciated
February 6th, 2012, 03:51 AM
February 6th, 2012, 09:35 AM
Originally Posted by swampBoogie
Thanks a lot , it worked. Can you please explain why do we need to use MIN function here?
February 6th, 2012, 09:40 AM
because you said you wanted col1 = 1 but not col1 = 3
Originally Posted by adi.shoukat
you could also use MAX or AVG -- the point is, you have to use ~some~ aggregate function if you want a value for col1 returned, because of the GROUP BY