DISTINCT keyword not removing duplicate rows in sql server 2005 temp table

Hi,
       I have  a query (see code) that returns a set of rows where there are many duplicates, however using DISTINCT does not remove the duplicates..
any help greatly appreciated!!!!!!
SELECT DISTINCT
Scheduled_Start_Date ,
Scheduled_End_Date ,
Change_ID ,
Summary ,
Notes  ,
PIR ,
Risk_Level ,
IMPACT ,
Region ,
SITE ,
TIMING,
Status ,
Requested_BY_First_Name  ,
Requested_By_Last_Name ,
Assignee_Support_Company ,
Assignee_Support_Organization  ,
Assignee_Support_Group_Name  ,
Change_Assignee  
FROM #FailedTab

Open in new window

blossomparkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JRandelCommented:
DISTINCT in your example may only work on the field Scheduled_Start_Date. However there are two other ways you could look at.

Using GROUP BY you can group rows by the fields you specify.
Another method is to use PARTITION. I can provide an example if required, but try GROUP BY first.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blossomparkAuthor Commented:
Hi, JRandel
tried
FROM #FailedTab
GROUP BY Change_ID

but doesn't work
----is invalid in the select list because it is not contained in either an aggrgate function or the GROUP BY clause
0
JRandelCommented:
When you use GROUP BY, unless you select fields by a function you can only SELECT those that you GROUP BY - for example:

SELECT field1, field2, field3
FROM table
GROUP BY field1, field2, field3
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

blossomparkAuthor Commented:
did GROUP BY
Scheduled_Start_Date ,
Scheduled_End_Date ,
Change_ID ,
Summary ,
Notes  ,
PIR ,
Risk_Level ,
IMPACT ,
Region ,
SITE ,
TIMING,
Status ,
Requested_BY_First_Name  ,
Requested_By_Last_Name ,
Assignee_Support_Company ,
Assignee_Support_Organization  ,
Assignee_Support_Group_Name  ,
Change_Assignee  

no error message now
but returning duplicate rows...
0
Anthony PerkinsCommented:
>>however using DISTINCT does not remove the duplicates..<<
Sure it does.  It just happens that DISTINCT considers the values in all the columns and not just specific ones of interest to you.

The best way to resolve this, is for you to post a sample (obfuscated if necessary) of your current output (using DISTINCT) and what is your expected result.  We can then help you more.
0
blossomparkAuthor Commented:
is there some way to remove duplicate rows based on a single column?
for example the CHANGE_ID column
0
Anthony PerkinsCommented:
>>is there some way to remove duplicate rows based on a single column?<<
There are a number of techniques to do that, such as using the or MAX() or MIN() aggregate functions on columns that you are not specifying in the GROUP BY clause or only including the first row based on some condition. But we really need to know your rules for this in order to give you a precise answer.
0
Anthony PerkinsCommented:
For example, supposing you have "duplicate" rows based on a particular column and do not care what value is picked for the remaining columns than you can use somthing like this:
SELECT	MAX(Scheduled_Start_Date) ,
	MAX(Scheduled_End_Date) ,
	Change_ID ,
	MAX(Summary) ,
	MAX(Notes),
	MAX(PIR),
	MAX(Risk_Level),
	MAX(IMPACT),
	MAX(Region) ,
	MAX([SITE]) ,
	MAX(TIMING),
	MAX([STATUS]) ,
	MAX(Requested_BY_First_Name),
	MAX(Requested_By_Last_Name),
	MAX(Assignee_Support_Company),
	MAX(Assignee_Support_Organization),
	MAX(Assignee_Support_Group_Name),
	MAX(Change_Assignee)
FROM	#FailedTab
GROUP BY
	Change_ID

Open in new window

0
Anthony PerkinsCommented:
However, ths may not be what you want.  For example:
Col1, Col2, Col3
1, four-legged, cat
1, two-legged, bird

SELECT Col1, MAX(Col2), MAX(Col3)
FROM YourTable
GROUP BY
             Col1

Would output this:
1, two-legged, cat
0
cyberkiwiCommented:
From SQL Server 2005 onwards, you would use the windowing function ROW_NUMBER to extract one row from multiple rows sharing a particular DISTINCT column(-set).

e.g.

SELECT
Scheduled_Start_Date ,
Scheduled_End_Date ,
Change_ID ,
Summary ,
Notes  ,
PIR ,
Risk_Level ,
IMPACT ,
Region ,
SITE ,
TIMING,
Status ,
Requested_BY_First_Name  ,
Requested_By_Last_Name ,
Assignee_Support_Company ,
Assignee_Support_Organization  ,
Assignee_Support_Group_Name  ,
Change_Assignee
FROM
(
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY Change_ID ORDER BY Scheduled_Start_Date DESC)
FROM #FailedTab) X
WHERE RN=1

Open in new window


Use the Partition By clause to specify which columns to break the result up by, and the ORDER BY to give a unique, increasing row number for each record within a partition.

Given the filter "WHERE RN=1", this will show only the record with the LATEST Scheduled_Start_Date for every Change_ID.
0
blossomparkAuthor Commented:
ok....thanks for all your comments....i have found that one of the columns has different data in every row (the PIR) column so therefore the returned rows are not actually duplicates? but are unique? sorry for having only discovered this now..i dont understand why one of the columns would have different values..i think i will have to go back to the "drawing board" on this one....thanks for all your comments, they really help and are thought provoking
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.