Duplicate records - 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 2000 Forums

Transact-SQL (2000)

Duplicate records

Author

Topic

MADTIES
Starting Member

6 Posts

Posted - 2006-02-26 : 17:16:02

Hello,

I have a table which contains dates and pairs of information for example.

Date Value1 Value2
01/01/2006 John David
01/01/2006 David John
20/02/2006 Chris Sarah
20/02/2006 Sarah Chris

Now for the purpose of the results I'm looking for, I need just 1 record from each pair i.e.

Date Value1 Value2
01/01/2006 John David
20/02/2006 Chris Sarah

It doesn't matter whether I have the 1st or 2nd item from each pair (just the same rule for every resulting record). Any suggestions would be much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-26 : 18:43:06

[code]select *
from yourtable t
where Value1 in (select top 1 Value1 from #yourtable x where x.Date = t.Date)[/code]

----------------------------------
'KH'
It is inevitable

MADTIES
Starting Member

6 Posts

Posted - 2006-02-26 : 18:56:34

Hi KH,

I think I made my example a little too simple. The problem is that the Value1 may appear in other combinations so a Top 1 would miss out certain records where the number of occurences is greater than 1.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-26 : 19:59:40

then can you post more samples and expected result please ?

----------------------------------
'KH'
It is inevitable

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-27 : 08:45:39

One thing you can do is write a SELECT that will always "sort" the names alphabetically: (Value1 will always be <= Value2)


select 
    Date, case when Value1 <= Value2 then Value1 else Value2 end as Value1,
          case when Value1 <= Value2 then Value2 else Value1 end as Value2
from
    YourTable

That will make rows with the same two names match up.

Then, using the above SQL in a derived table, you can do a simple group by :


select Date, Value1, Value2
from
  (the above SQL) a
group by Date, Value1, Value2

MADTIES
Starting Member

6 Posts

Posted - 2006-02-27 : 19:03:56

Thanks JSmith8858, I had actually thought of something similar after pondering on the problem and used the < operator to compare the 2 fields and then select 1 of these. As both records would have this same one, I could then filter on this.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources