Need to return the most recent sale for each salesman
Posted on 2008-10-21
I have the following data:
TableAutoID SalespersonID Sale Date
83 388 2008-10-03 12:43:25.320
84 481 2008-10-03 12:50:04.807
85 481 2008-10-03 12:51:01.150
86 100 2008-10-03 12:50:04.807
87 100 2008-10-03 12:43:25.320
... and so on, but that's enough to indicate what I need.
I need to return the TableAutoId of the most recent sale for each salespersonID, so based on the records shown above, I need to return 83, 85 and 86. I've gone round and round trying to find some way to do this in a query WITHOUT A CURSOR, and I think it can be done with some sort of correlated subquery selecting from an aliased version of the original table, but thus far, I haven't come up with the solution to what seems like a fairly common query. Thus, I have come to grovel at the feet of the experts at experts exchange. Grovel, grovel.