March 7, 2012 at 3:24 pm
Hi folks,
I have a table Infosys with the following fields: code, name, description
There are probably some records with same name and description but different code, I want to select them out, how should I write the query?
Thanks.
March 7, 2012 at 3:26 pm
to see them all, i'd use row_number:
select row_number() over (PARTITION BY name, description ORDER BY code, name, description) As RW,
code, name, description
FROM YOURTABLE
from that same query, the ones with RW > 1 are your duplicates:
SELECT * FROM
(
select row_number() over (PARTITION BY name, description ORDER BY code, name, description) As RW,
code, name, description
FROM YOURTABLE
)MyAlias
WHERE RW > 1
Lowell
March 7, 2012 at 4:31 pm
Thanks. but the query returns only a few redundant names, most of the results are not the same [name]/description with different code
March 7, 2012 at 6:41 pm
How about the DDL for the table, some sample data, the expected results, and what you have done so far to solve your problem.
March 8, 2012 at 8:23 am
I would really love to post the data here but I just can't, simple due to the security sensitivity of the data
March 8, 2012 at 8:32 am
OK, here are the records in the table:
Name Description Code
Research Portal - CMSEquity Research publications Content Management System (internal) and xxxx.com Research site (internet facing).VP
Research Portal - DMSEquity Research publications Document Management System (HOSTED).VQ
Research Portal - CMSEquity Research publications Content Management System (internal) and xxxx.com Research site (internet facing).B68M
The three codes are VP, VQ and B68M, their respective names are:
VP: Research Portal - CMS
VQ: Research Portal - DMS
B68M: Research Portal - CMS
The query picks up VP and VQ, and missed B68M
The query should indeed return VP and B68M
Thanks.
March 8, 2012 at 8:40 am
Dear halifaxdal, I cannot believe, that with visiting SQLServerCentral over 1000 times and accumulating almost 600 points you have never seen the aricle about posting questions... There is a link at the bottom of my signature.
I'm pretty sure, that if you follow the advices from the above article, you will see relevant help to your question in almost no time.
BTW, you don't need to post your real data, but you should be able to come up with some representable fakes...
March 8, 2012 at 8:48 am
This is my code and it picks up right records, but I don't really like my code, looks cumbersome and not neat to me, maybe someone can rewrite it for me? Thanks.
select i.[name], Description, Code from infosys i inner join
(select [name] as [name], count(1) as total from infosys
group by [name]
having count(1) > 1) r on r.[name] = i.[name]
order by [name]
March 8, 2012 at 8:51 am
Eugene Elutin (3/8/2012)
Dear halifaxdal, I cannot believe, that with visiting SQLServerCentral over 1000 times and accumulating almost 600 points you have never seen the aricle about posting questions... There is a link at the bottom of my signature.I'm pretty sure, that if you follow the advices from the above article, you will see relevant help to your question in almost no time.
BTW, you don't need to post your real data, but you should be able to come up with some representable fakes...
ooop, first complaint in the new year. I take it. Thanks.
March 8, 2012 at 8:52 am
Your code is fine to me, but you can use CTE to make it "look nice":
;with duplicates
as
(
select [name] from infosys
group by [name]
having count(*) > 1
)
select i.[name], i.Description, i.Code
from infosys i
join duplicates d
on d.[name] = i.[name]
order by i.[name]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply