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
 Urgent help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

winniepa
Starting Member

India
4 Posts

Posted - 03/05/2014 :  10:09:02  Show Profile  Reply with Quote
Hi,

I have written the sql below to fetch duplicate records grouped.
Is there any other way to write this?Can duplicates be fetched using looping?

select unique,id,network, code from Networkentity where
en_id =6 and
id in (select id from (
select id,unique, count(id) from network where en_id
=6 group by unique, id having count(id)>1)) group by
unique,id,code,network
order by unique,id

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/05/2014 :  11:13:42  Show Profile  Reply with Quote
If your ID is unique and it is duplicating then use RANK() or DENSE_RANK function on ID column. Then you'll be able to see which IDs are duplicating.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page

winniepa
Starting Member

India
4 Posts

Posted - 03/05/2014 :  11:20:51  Show Profile  Reply with Quote
Hey,

Do you mind giving an example??if you could use my query that would be great please!!
Go to Top of Page

winniepa
Starting Member

India
4 Posts

Posted - 03/05/2014 :  11:24:18  Show Profile  Reply with Quote
yes my id is uniquUnique is a column that countains unique id's,under which comes the column 'id' which is mainly duplicated here and i need records which has duplicate 'id' values and those id values come under 'unique' columnheads or are rather grouped under various 'unique' columnheads,hope i am able to explain what is needed here..please spare some time and give an example.
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/05/2014 :  11:28:59  Show Profile  Reply with Quote
SELECT UNIQUE, id, DENSE_RANK() OVER(Partition by id Order by id) AS ID_Rank, network, code from Networkentity .....

Now the ID_Rank will show you the duplicates.

I haven't checked your query though. So I am assuming that your query works correct prior to applying this new column.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page

winniepa
Starting Member

India
4 Posts

Posted - 03/05/2014 :  11:31:25  Show Profile  Reply with Quote
ya it worked correct previosuly..thanks a ton !!! :)
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.08 seconds. Powered By: Snitz Forums 2000