?
Solved

Need to return the most recent sale for each salesman

Posted on 2008-10-21
4
Medium Priority
?
295 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:nedrich12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
jfmador earned 2000 total points
ID: 22773019
Try this

SELECT TableAutoID FROM table
INNER JOIN
(SELECT SalesPersonID, MAX(SaleDate) as MAxSaleDate FROM table GROUP BY SalesPersonID) t
ON table.SalesPersonID = t.SalesPersonID and table.SaleDate = t.SaleDate
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 22773022
try this
select * from utable
where [Sale Date]=(select max([Sale Date]) 
from utable as t where t.SalespersonID=utable.SalespersonID)

Open in new window

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22773024
Sorry i made a mistake

SELECT TableAutoID FROM table
INNER JOIN
(SELECT SalesPersonID, MAX(SaleDate) as MAxSaleDate FROM table GROUP BY SalesPersonID) t
ON table.SalesPersonID = t.SalesPersonID and table.SaleDate = t.MaxSaleDate

I mispelled a field from t
0
 

Author Closing Comment

by:nedrich12
ID: 31508589
Thanks for your help. This is exactly what I needed.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question