Solved

Need to return the most recent sale for each salesman

Posted on 2008-10-21
4
290 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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

695 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