if repeats more than once, display only once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if repeats more than once, display only once

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-27 : 10:39:25

I have a stored procedure similar to below :


select field1 from table1
group by field1
having count(field1) = 1

I just realized that this query actually completely eliminates records if field1 is repeated twice, but actually if they repeated i want to display once.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 10:40:25

then this is sufficient

select field1 from table1
group by field1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-27 : 10:49:04

thanks visakh. It was so simple but I could not make it.

But in case if I have a query like below, how it can be done


select field1, field2, field3 from table1
group by field1, field2, field3
having count(field1) = 1 or count(field2) = 1

In the above code, if field1 individually repeats more than once or field2 individually repeats more than once or
field1 and field2 combinely repeats more than once, I want to display any one of them.

The above code displays some output but I still cannot find out if the result is really like how I want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 10:55:57

do you mean this?


select field1,field2,field3
from
(
select field1,field2,field3,
count(1) over (partition by field1) as field1cnt,
count(1) over (partition by field2) as field2cnt,
row_number() over (partition by field1 order by field1) as field1seq,
row_number() over (partition by field2 order by field2) as field2seq
from table1
)t
where (field1cnt>1
and field1seq=1)
or (field2cnt>1
and field2seq=1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-29 : 10:04:18

@visakh16. Thank you.

I can't understand where is wrong with the code, but it displays nothing "0 rows affected"

Basically, I have three columns field1, field2, field3 and I want to make sure if they have duplicates values, it shall show only once in the output.

The code below works but it considers duplicate only if all three fields are repeated.


select field1, field2, field3 from table1
group by field1, field2, field3

I want to make sure even any one field is repeated, it shall consider as duplicate irrespective of what values in the other two fields and it should display any one value
for e.g. if
it has values "25, abc, red" and "25, xyz, yellow" respectively. It shall still consider as duplicate and any one value shall be displayed maybe the first one or the second one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources