SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 if repeats more than once, display only once		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

155 Posts
Posted - 09/27/2012 :  10:39:25  Show Profile  Reply with Quote
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

India
47189 Posts
Posted - 09/27/2012 :  10:40:25  Show Profile  Reply with Quote
then this is sufficient

select field1 from table1
group by field1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

learning_grsql
Posting Yak Master

155 Posts
Posted - 09/27/2012 :  10:49:04  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 09/27/2012 :  10:55:57  Show Profile  Reply with Quote
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/
Go to Top of Page

learning_grsql
Posting Yak Master

155 Posts
Posted - 09/29/2012 :  10:04:18  Show Profile  Reply with Quote
@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.
Edited by - learning_grsql on 09/29/2012 10:05:32
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000