Okay, I can restate this in a more simple way. If I have a table like:
EmpSSN    EmpID    LastTimeIn
001624633    13864    01/28/05
020489644    2897    01/28/05
020489644    3582    12/13/05
041468792    3507    04/05/05
041468792    3614    06/24/05
057627194    3150    06/21/05
057627194    3694    09/20/05
057627194    4081    12/21/05
057729267    3183    07/27/05
057729267    3721    12/20/05

I want to find all of the EmpID's for any given EmpSSN, except the one
EmpID with the most recent LastTimeIn

So, the result set is

020489644    2897    01/28/05
041468792    3507    04/05/05
057627194    3150    06/21/05
057627194    3694    09/20/05
057729267    3183    07/27/05

How do I do that?

-John

First, you need to identify maximum value for the LastTimeIn for each
EmpSSN.  In this case, we can create a view for this.

Create view Vw_MaxTimeIn as
select EmpSSN, max(LastTimeIn) as MaxTimeIn
from TableA
group by EmpSSN

Next, you can write a query to get back everything in the table except
the values in the first query.

select a.*
from TableA a left join Vw_MaxTimeIn b on a.EmpSSN = b.EmpSSN and
a.LastTimeIn = b.MaxTimeIn
where b.MaxTimeIn is null

Show quote Hide quote I like that but the TableA is dynamically created with another query.
Can I still do something like that?

-John

You will probably want to use temp tables instead of views, but yes,
that will work.

Why query optimizer does not select the index?
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation
Deadlock updating different rows