| J. Clarke 2005-03-30, 7:03 pm |
| Wow! Thanks for the explaination and the example. Your example I think may
work for the best of me. From one example I was peeking at I may up to 4000
'dups' off the 1st record (yikes!). I'm concerned tho, that I may have an
actual record on another day and I need to ensure I'm not including those
puppies
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:b532bdf904ce437
fb849575d7ccfd8fb@SQ
LMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
>
> if exists(select name from sysobjects where name = 'MyTable' and type =
> 'U')
> drop table MyTable
> go
>
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
>
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
>
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
>
> select * from MyTable
>
> --
> Message posted via http://www.sqlmonster.com
|