DISTINCT Still gives me dups records in results

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 2008 Forums

Transact-SQL (2008)

DISTINCT Still gives me dups records in results

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-02 : 01:17:06

select DISTINCT p.PartNum,
p.PartID,
pn.Name,
d.[Description],
n.Note as PartNote
from Part p
join PartName pn on pn.PartNameID = p.PartNameID
join ApplicationPaint ap on ap.partID = p.PartID
join [Application] a on a.ApplicationID = ap.ApplicationID
join [Description] d on d.DescriptionID = ap.DescriptionID
join Note n on n.NoteID = a.NoteID
join MYConfig mmy on mmy.MMYConfigID = a.MYConfigID
join Model mo on mo.ModelID = mmy.ModelID
where mmy.ModelId = 2673
and substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note))= 'Johnson'

results:

T50015 765963 Some Part Name SomeNoteA [342] Johnson
T50015 765963 Some Part Name SomeNoteA [343] Johnson
T60024 766068 Some Part Name SomeNoteB [342] Johnson
T60024 766068 Some Part Name SomeNoteB [343] Johnson
T60231 766093 Some Part Name SomeNoteA [342] Johnson
T60231 766093 Some Part Name SomeNoteA [343] Johnson
T60232 766094 Some Part Name SomeNoteA [342] Johnson
T60232 766094 Some Part Name SomeNoteA [343] Johnson
T70134 766150 Some Part Name SomeNoteA [342] Johnson
T70134 766150 Some Part Name SomeNoteA [343] Johnson
T70230 766153 Some Part Name SomeNoteC [342] Johnson
T70230 766153 Some Part Name SomeNoteC [342] Johnson
T70230 766153 Some Part Name SomeNoteC [343] Johnson
Y50078 766253 Some Part Name SomeNoteH [342] Johnson
N30026 766352 Some Part Name SomeNoteT [342] Johnson
N30026 766352 Some Part Name SomeNoteT [343] Johnson
N50041 766465 Some Part Name SomeNoteK [342] Johnson
N50041 766465 Some Part Name SomeNoteK [343] Johnson
N60176 766499 Some Part Name SomeNoteX [342] Johnson
N60176 766499 Some Part Name SomeNoteX [343] Johnson
N60750 766503 Some Part Name SomeNoteU [342] Johnson
N60750 766503 Some Part Name SomeNoteU [343] Johnson

so I'm getting dups even triples on every PartNumber

T70230 766153 Some Part Name SomeNoteC [342] Johnson
T70230 766153 Some Part Name SomeNoteC [342] Johnson
T70230 766153 Some Part Name SomeNoteC [343] Johnson

T50015 765963 Some Part Name SomeNoteA [342] Johnson
T50015 765963 Some Part Name SomeNoteA [343] Johnson

so what I want to see is this:

T50015 765963 Some Part Name SomeNoteA [342] Johnson
T60024 766068 Some Part Name SomeNoteB [342] Johnson
T60231 766093 Some Part Name SomeNoteA [342] Johnson
T60232 766094 Some Part Name SomeNoteA [342] Johnson
T70134 766150 Some Part Name SomeNoteA [342] Johnson
T70230 766153 Some Part Name SomeNoteC [342] Johnson
Y50078 766253 Some Part Name SomeNoteH [342] Johnson
N30026 766352 Some Part Name SomeNoteT [342] Johnson
N50041 766465 Some Part Name SomeNoteK [342] Johnson
N60176 766499 Some Part Name SomeNoteX [342] Johnson
N60750 766503 Some Part Name SomeNoteU [342] Johnson

So I want only one unique row for each Unique part number, not dup part number rows showing here.

So to put it in other words for example I want this (one row only for a partID):

T70230 766153 Some Part Name SomeNoteC [342] Johnson

vs. dups:

T70230 766153 Some Part Name SomeNoteC [342] Johnson
T70230 766153 Some Part Name SomeNoteC [342] Johnson
T70230 766153 Some Part Name SomeNoteC [343] Johnson

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-11-02 : 01:24:09

I believe this question was just asked.

If all columns are the same by adding the word DISTINCT to your select it will only give one record, so something is not.

You can get only 1 records for each part number by doing this.


select 
PartNum,
PartID,
Name,
[Description],
PartNote
from
(
select Row_Number() over (partition by p.partNum order by p.partNum) as RowID,
p.PartNum,
p.PartID,
pn.Name,
d.[Description],
n.Note as PartNote
from Part p
join PartName pn on pn.PartNameID = p.PartNameID
join ApplicationPaint ap on ap.partID = p.PartID
join [Application] a on a.ApplicationID = ap.ApplicationID
join [Description] d on d.DescriptionID = ap.DescriptionID
join Note n on n.NoteID = a.NoteID
join MYConfig mmy on mmy.MMYConfigID = a.MYConfigID
join Model mo on mo.ModelID = mmy.ModelID
where mmy.ModelId = 2673
and substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note))= 'Johnson'
) aa
where aa.rowid = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-02 : 01:29:17

yea would prefer no sub selects. I actually found that one of the columns did not have the same value as the other so it was keeping the dups in.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-11-02 : 01:54:28

quote:

Vinnie881
"If all columns are the same by adding the word DISTINCT to your select it will only give one record, so something is not."

Of course you would not do a sub-select, it is not needed. The query was to show how to get the results you requested without knowing what column was causing the records to not be unique. The word DISTINCT in a select clause will ALWAYS only show 1 record if columns are identical, so if you have multiple then a record is NOT a duplicate so double check it's normally a simple fix.

Glad it is now working for you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources