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.
Author |
Topic |
winniepa
Starting Member
4 Posts |
Posted - 2014-03-05 : 10:09:02
|
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 whereen_id =6 andid in (select id from (select id,unique, count(id) from network where en_id=6 group by unique, id having count(id)>1)) group byunique,id,code,networkorder by unique,id |
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-03-05 : 11:13:42
|
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)_! |
|
|
winniepa
Starting Member
4 Posts |
Posted - 2014-03-05 : 11:20:51
|
Hey,Do you mind giving an example??if you could use my query that would be great please!! |
|
|
winniepa
Starting Member
4 Posts |
Posted - 2014-03-05 : 11:24:18
|
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. |
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-03-05 : 11:28:59
|
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)_! |
|
|
winniepa
Starting Member
4 Posts |
Posted - 2014-03-05 : 11:31:25
|
ya it worked correct previosuly..thanks a ton !!! :) |
|
|
|
|
|
|
|