Solved

Need to return the most recent sale for each salesman

Posted on 2008-10-21
4
272 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
  • 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now