Finding duplicate data - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding duplicate data

Author

Topic

xIcarus
Starting Member

5 Posts

Posted - 2007-09-26 : 13:46:52

Greetings,

I am trying to find duplicate data with duplicate being defined based on a single column. I have a column of numerical data (Item #'s) and a description of what the item is that I am trying to SELECT. The data is an item list for a convenience store. I am trying to select (and display!) all of the items which are in the table multiple times regardless of what the description says (and return the description as well). The hard part for me here is that I'm trying to display all the duplicate rows so I can't use a GroupBy clause. If it helps at all, the reason I am doing this is the store uses duplicate item #'s to link items together to make price changes easier. I want to get a list of all of the duplicated item #'s that is sorted by item number and a description for each so we can determine if items are linked that should not be. Any help would be greatly appreciated. I'd imagine this is pretty simple and I'm just missing something in my logic. Thank you! - Josh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 13:51:19

http://www.sqlteam.com/article/deleting-duplicate-records

We can help you with the problem of displaying the actual rows that are duplicates if you could show us the DDL of the table and exactly which column shows the duplicate and what columns you want displayed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

xIcarus
Starting Member

5 Posts

Posted - 2007-09-26 : 13:58:11

Sorry for not making it clear enough.

ITEM# DESCRIPTION
10001 cat food - steak
10001 cat food - chicken
10001 cat food - fish
10002 balloons
10003 marlboro cigarettes
10004 lollipop
10004 lollipop
10004 lollipop
10005 canned soup

Okay. So this is a set of sample data. I want the query to select the item #'s with duplicates and display all of the item numbers for those duplicates and the description. Note the items with the same item number AND description. This does occur and I would like them to be displayed as well if at all possible (there ARE other fields in the table so these are not completely duplicate rows). I've been having a lot of issues with this query. Anyway, if the query ran the way I wanted it to on the above data, the query would result as:

ITEM # DESCRIPTION
10001 cat food - steak
10001 cat food - chicken
10001 cat food - fish
10004 lollipop
10004 lollipop
10004 lollipop

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:08:59

[code]DECLARE @Sample TABLE (Item INT, Descr VARCHAR(80), OtherColumn UNIQUEIDENTIFIER)

INSERT @Sample
SELECT 10001, 'cat food - steak', NEWID() UNION ALL
SELECT 10001, 'cat food - chicken', NEWID() UNION ALL
SELECT 10001, 'cat food - fish', NEWID() UNION ALL
SELECT 10002, 'balloons', NEWID() UNION ALL
SELECT 10003, 'marlboro cigarettes', NEWID() UNION ALL
SELECT 10004, 'lollipop', NEWID() UNION ALL
SELECT 10004, 'lollipop', NEWID() UNION ALL
SELECT 10004, 'lollipop', NEWID() UNION ALL
SELECT 10005, 'canned soup', NEWID()

;WITH Yak (Item, [Description], OtherColumn, Items)
AS (
SELECT Item,
Descr,
OtherColumn,
COUNT(*) OVER (
PARTITION BY Item,
CASE CHARINDEX(' - ', Descr)
WHEN 0 THEN Descr
ELSE LEFT(Descr, CHARINDEX(' - ', Descr))
END
)
FROM @Sample
)

SELECT Item,
[Description],
OtherColumn
FROM Yak
WHERE Items > 1
ORDER BY Item,
[Description][/code]

E 12°55'05.25"
N 56°04'39.16"

xIcarus
Starting Member

5 Posts

Posted - 2007-09-26 : 14:43:03

I am having a lot of trouble getting that to work for some reason. When I try to run that query I get an error about a SELECT statement being expected alongside the WITH clause. Could someone either help me think of another solution or maybe give me more information about what that solution does so I can figure out where it is going wrong? Thank you all again for your assistance on this. -Josh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 14:48:22

Posting the error is always helpful.

You are using SQL Server 2005, right? And your database's compatibility level is set to 90?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:48:37

If I copy and paste the code into SSMS (SQL Server 2005 since this is a SQL Server 2005 forum),
the query works.

Are you using another RDBMS?

E 12°55'05.25"
N 56°04'39.16"

xIcarus
Starting Member

5 Posts

Posted - 2007-09-26 : 14:53:43

I'm using SQL Server 2005 and the error I am getting is "Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"

I also don't have access to the database as admin to be able to change or check the compatibility level.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:03:05

EXEC sp_dbcmptlevel 'my databasename here'

E 12°55'05.25"
N 56°04'39.16"

xIcarus
Starting Member

5 Posts

Posted - 2007-09-26 : 15:07:51

That resolved the issue. Thank you both kindly for your responses. Can someone tell me what the compatibility level flag does? Is there any reason it should be set to anything other than 90? Just so I can get in touch with my admin and run it by him.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 15:14:26

That query should not have resolved anything. It was meant to only check the compatibility level, so that we could find out what we are dealing with.

Check BOL for details on compatibility levels.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources