I need to construct a sql query for the following problem. I have a
table of employees with columns: EmployeeID, Name, Department,
Address, TimeDateStamp.  When the address is updated, rather than
replacing the row, I add a new entry for the employee with the updated
address. However, when I do a search to find all employee addresses
for a given Department, I only want the most recent address listed for
each employee. What would the query be? (I believe a nested query is
required).

---

Request for Question Clarification by hammer-ga on 11 Apr 2005 12:41 PDT

brainsurgeon-ga,

Which version of which database package are you using?

- Hammer

---

Clarification of Question by brainsurgeon-ga on 11 Apr 2005 17:52 PDT

I am connecting to a SQL Server 2000 database using a SQL query in Dreamweaver.

Thanks.

Hi, 

This should do it:

select  employeeID, Name, Department, Address, max(TimeDateStamp)
from tablename 
group by employeeID, Name
order by Name 

webadept-ga

---

Clarification of Answer by webadept-ga on 12 Apr 2005 05:37 PDT

By the way, ... 

You will want to have indexes on the employeeID and timeDateStamp
fields with the setup you are describing. Non-unique of course, but
indexes just the same.

If things start to slow down in the future, you might want to consider
saving address/employee history in a seperate table, and keeping the
table which has the most queries per day as unique as possible, with
clean/fast unique indexes. Just a suggestion, your milage may vary.

webadept-ga

With all due respect webadept, you may have rushed through this answer
a bit too quickly.  Notice you are selecting fields in your query that
are neither calculated nor in the group by clause.  As such, the query
you suggested is invalid.

In any case, a subquery is required and a single table query approach
will not suffice for what the questioner is asking.  Even if you
modified your query to the following:
    select  employeeID, Name, Department, Address, max(TimeDateStamp)
    from tablename 
    group by employeeID, Name, Department, Address
    order by Name 
.. we would not get only one address for each employee as required.

The following test script contains a working query to get the proper results:

create table Employees (
  EmployeeID int,
  Name varchar(50),
  Department varchar(50),
  Address varchar(50),
  TimeDateStamp smalldatetime )

insert into Employees (EmployeeID, Name, Department, Address, TimeDateStamp)
  values ('1','John Smith','ar','123 Nowhere','1/1/2003')
insert into Employees (EmployeeID, Name, Department, Address, TimeDateStamp)
  values ('1','John Smith','ar','456 Anywhere','6/30/2004')

-- THIS QUERY WILL GET THE LATEST DEPARTMENT AND ADDRESS FOR EACH EMPLOYEE
select	e.EmployeeID,
	Name,
	Department,
	Address,
	LastUpdate
from	Employees e
join (	select	EmployeeID,
		max(TimeDateStamp) as LastUpdate
	from	Employees
	group by
		EmployeeID ) lu
  on	(e.EmployeeID = lu.EmployeeID and e.TimeDateStamp = lu.LastUpdate)