CODEWITHSUNDEEP
sqloracle-databaseplsql
bd528
bd528

Reputation: 886

Return records where there are no duplicates in one field

I have the following (sample) script

Select field1, field2, field3, field4, field5 from tblsample

which may return

field1, field2, field3, field4, field5
123     etc     etc     etc     etc
123     etc     etc     etc     etc
123     etc     etc     etc     etc
456     etc     etc     etc     etc
789     etc     etc     etc     etc
789     etc     etc     etc     etc
125     etc     etc     etc     etc

How should I write a query to return only those rows which don't have duplicate values in "field 1". for example for the table above the expected result is:

field1, field2, field3, field4, field5
456     etc     etc     etc     etc
125     etc     etc     etc     etc

please note that the rows with 123 and 789 are not included in the result, since there are multiple rows with those values.

Upvotes: 0

Views: 66

Answers (5)

sia
sia

Reputation: 577

without using window function:

SELECT *
 FROM tblsample t INNER JOIN (SELECT
                            count(field1),
                            field1
                          FROM tblsample
                          GROUP BY field1
                          HAVING count(field1) = 1) a
ON a.field1 = t.field1

Upvotes: 0

Tony Montana
Tony Montana

Reputation: 39

select * 
from (select *,COUNT(*) over (partition by field1) row 
from tblsample) x 
where row = 1

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12684

Without using max() function;

select *
from tblsample
where field1 in 
(select field1 
 from tblsample 
 group by field1 
 having count(1)=1);

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Use analytic count() in such situations:

demo

select field1, field2, field3, field4, field5
  from (select field1, field2, field3, field4, field5, 
               count(1) over (partition by field1) cnt
          from tblsample)
  where cnt = 1

Upvotes: 1

user2671057
user2671057

Reputation: 1533

Select field1, max( field2), max(field3), max(field4), max( 
 field5)
 from tblsample
group by field1
having count(*) = 1;

Upvotes: 0

Related Questions