query

I haven't done this for a while and i am stuck, any help would be appriciate
d.
I need to return the last employee id for each record in my database. Here
is my table.
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id.
Only 1 employee per Appt_ID. Any help would be great! Thanks!Try,
select Appt_ID, Employee_ID, Time_Stamp
from t1 as a
where Time_Stamp = (select max(b.Time_Stamp) from t1 as b where b.Appt_ID =
a.Appt_ID)
AMB
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Hi
you can do it as:
SELECT Appt_ID, MAX(Employee_ID)
FROM <EmployeeTable>
GROUP BY appt_ID
please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Apearently, On the basis of provided data Chandra's query works fine.
But I think that solution from Alejandro Mesa is the correct one
because Chandra, if we use MAX(Employee_ID), it will always means
Maximum for a perticular appt_ID, where as "last employee" should be in
respect to Max(Time_Stamp).|||thats true, but time stamp might take a new value when the data is updated
too. the new emplyee will have a max number.
that was my assumption
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Zero.NULL" wrote:

> Apearently, On the basis of provided data Chandra's query works fine.
> But I think that solution from Alejandro Mesa is the correct one
> because Chandra, if we use MAX(Employee_ID), it will always means
> Maximum for a perticular appt_ID, where as "last employee" should be in
> respect to Max(Time_Stamp).
>|||The employee could change internally to an existing employee. Think of the
situation as an account manager where accounts could be transfered between
then for various reasons. I just need a single appointment id, the id of th
e
current employee handing the account which would be based off the highest
date time stamp. Hope this helps.
"Chandra" wrote:
> thats true, but time stamp might take a new value when the data is updated
> too. the new emplyee will have a max number.
> that was my assumption
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Zero.NULL" wrote:
>|||Chandra, I understand, according to your assumption the query is
perfect. But as
B. A. Barakus has cleared his stand on this, I think he is loking for
such a query given by Alejandro Mesa.
Ok I try to clear it more, If his table is like this:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
and if Employee_ID gets changed from 12 to any ID less then 12 (say 6)
for Appt_ID: 2 and Time_Stamp: 7/30/05
the data would look like:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 6 7/30/05
3 2 8/2/05
now query by Alejandro Mesa will fetch you
1 5 7/25/05
2 6 7/30/05
3 2 8/2/05
where as query by Chandra gets:
1 5 7/25/05
2 7 7/30/05
3 2 8/2/05
Hope this solves the issue.