duplicate records ... sort of 1

[Gooser]

I have data that is similar to this:

this_id  eff_date  other_columns
-------- --------- --------------
1        1/1/2000  blah, blah, blah
2        1/1/2000  yada, yada, yada
2        1/1/2004  dobe, dobe, dooo
3        1/1/2001  long, long, long
4        1/1/2005  this, that, uder
4        1/1/2009  this, dont, matr

What I want to get back is

this_id  eff_date  other_columns
-------- --------- --------------
2        1/1/2000  yada, yada, yada
2        1/1/2004  dobe, dobe, dooo
4        1/1/2005  this, that, uder
4        1/1/2009  this, dont, matr

or, just the rows where this_id repeats because the other columns are dissimilar.

I think I've done something like this before, but today it has me stumped.

Thanks much,
Gooser

 

[gmmastros]

First, forget about all the other columns. Get the rows where there are duplicate Id's.

Select this_ID
From   YourTable
Group By this_ID
Having Count(*) > 1

Next, make this a derived table to get your results.

Select YourTable.*
From   YourTable
       Inner Join (
         Select this_ID
         From   YourTable
         Group By this_ID
         Having Count(*) > 1
         ) As Dups
         On YourTable.this_id = Dups.this_id

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom