duplicates issue 1

[ethorn10]

Hello,

I know this should not be this tough but I can't seem to wrap my head around it. I am trying to remove duplicates but it is more than just that...

I have a result set of:

ID     Name     SetDate     ExpireDate     AnotherDate     Reason
-------------------------------------------------------------------
21     test     2/16/2005   2/16/2008        6/17/2005     reason1
21     test     2/16/2005   2/16/2008        11/21/2005     reason2
21     test     2/16/2005   2/16/2008        1/08/2006     reason3
21     test     2/16/2005   2/16/2008        2/08/2006     reason2

I'm trying to get the ID, name, setdate, expiredate and the max anotherdate plus the reason that goes with it. And to be honest...I can't even remember what I've tried at this point. Thanks for any help and don't be too harsh on me...

 

[vongrunt]

Get MAX() aggregate, join it back on all grouped columns. Something like:

select A.*
from blah A
inner join
(	select ID, Name, SetDate, ExpireDate, Max(AnotherDate) as MaxAnotherDate
	from blah
	group by ID, Name, SetDate, ExpireDate
) B on A.ID = B.ID
	and A.Name = B.Name
	and A.SetDate = B.SetDate
	and A.ExpireDate = B.ExpireDate
	and A.AnotherDate = B.MaxAnotherDate

(not tested)

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ethorn10]

vongrunt -- thank you for your quick reply. i'll go test this and let you know my findings. thanks again...

 

[ethorn10]

ok...your query worked as expected however when i ran it i noticed a new caveat. in a few instances, an id may show up twice because it has two separate "reasons" that have the same "another date."

firstly...a star for your help.

and i'll give another if we can eliminate the new dupes as described above.

 

[vongrunt]

Either add some other existing column(s) into GROUP BY and join, or distinct everything (SELECT DISTINCT A.* FROM... )

First method is better, second one is easier

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ethorn10]

Ok hopefully you can continue to straighten me out here...

I seem to have been slightly off in my description of what I wanted...and this is due to lack of sleep/coffee. Using an extremely similar result set as listed above:

ID     Name     SetDate     ExpireDate     AnotherDate     Reason
-------------------------------------------------------------------
21     test     2/16/2005   2/16/2008        6/17/2005     reason1
21     test     2/16/2005   2/16/2008        11/21/2005     reason2
21     test     2/16/2005   2/16/2008        1/08/2006     reason3
21     test     2/16/2005   2/16/2008        1/08/2006     reason2

I want to eliminate duplicates (which vongrunt eliminated all but two -- which helped me realize my error in my explanation and I apologize) and show only the latest "anotherdate."

vongrunt, your solution is 98% there ... I just can't eliminate these last few. They occur when there is a matching "anotherdate" and different "reasons" which makes the select DISTINCT not work because they are distinct and there are no other columns to group by or join on. Thanks again...

 

[vongrunt]

Argh, I hate duplicates!

There are two rows with same ID, NAME, SetDate, ExpireDate, AnotherDate - but different Reason values. Which one is correct: reason3 or reason2? Or any of these two?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[ethorn10]

That's the biggest issue....I don't know!! Let me re-phrase that...THEY don't know!! Crazy ass users. I came up with a dirty hack but I'm sure I'll be changing it in the near future.

hack =

CASE WHEN a.anotherdate = b.maxanotherdate THEN MAX(reason) END AS reason

I'm well aware that this isn't pretty nor very reliable but I'm going with it for now as either reason is valid for the time being. Thanks again for your headaches vongrunt, another star!