Column Comparison

I'm in need of a sql query that I'm not sure is possible. Here is an
example of how it's laid out.

employee ID Job class Last Change Date
12345 x 2/1/2004
12345 y 1/15/2004
12345 z 1/1/2004

We know that this person is in job class 'x' because it's the most
recent change. Is there a way to write a query that will exclude the
lines 'y' and 'z' because they are currently incorrect?

I would appreciate any help I could get. ThanksMatt (mjreiter@.yahoo.com) writes:
> I'm in need of a sql query that I'm not sure is possible. Here is an
> example of how it's laid out.
>
> employee ID Job class Last Change Date
> 12345 x 2/1/2004
> 12345 y 1/15/2004
> 12345 z 1/1/2004
> We know that this person is in job class 'x' because it's the most
> recent change. Is there a way to write a query that will exclude the
> lines 'y' and 'z' because they are currently incorrect?

SELECT t.empolyeeid, t.job_class, t.lastchangedate
FROM tbl t
JOIN (SELECT employeeid, lastchangedate = MAX(lastchangedate)
FROM tbl
GROUP BY employeeid) AS x
ON t.employeeid = x.employeeid
AND t.lastchangedate = m.lastchangdate

What you see in the middle is a derived table. This a very powerful
feature in SQL. It is sort of a temporary table in the middle of the
query, but it is not matierialized as such, and the optimizer may find
shortcuts so that the entire table is never computed, only what is
needed for the query.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp