sql server misc: Hello;

I have a question about tracking computers and their respective
organizational units.

The following table is populated nightly from Active Directory.

create table computer_ou (
computername varchar (20) not null,
ou varchar (50) not null,
scandate datetime not null
)

Sample data:
insert computer_ou values ('w1', 'ou1', '2005-09-15')
insert computer_ou values ('w1', 'ou1', '2005-09-16')
insert computer_ou values ('w1', 'ou2', '2005-09-17')
insert computer_ou values ('w1', 'ou2', '2005-09-18')
insert computer_ou values ('w1', 'ou3', '2005-09-19')
insert computer_ou values ('w1', 'ou3', '2005-09-20')
insert computer_ou values ('w1', 'ou1', '2005-09-21')
insert computer_ou values ('w1', 'ou1', '2005-09-22')

I would like the following as output:

computername ou scandate
------------- ---- ----------
w1 ou1 2005-09-15
w1 ou2 2005-09-17
w1 ou3 2005-09-19
w1 ou1 2005-09-21

(4 row(s) affected)

The problem I'm having is when a computer account is moved from one OU
and then back into the original OU again (in my sample, ou1). The
following:

select
computername
, ou
, min(scandate) as scandate
from computer_ou
group by computername, ou

yields:

computername ou scandate
------------- ---- ----------
w1 ou1 2005-09-15
w1 ou2 2005-09-17
w1 ou3 2005-09-19

(3 row(s) affected)

I know there must be an elegant set based solution, but I'm having a
hard time thinking of one.

Thanks;
Nick

Replied in microsoft.public.sqlserver.programming

--
David Portas
SQL Server MVP
--