Solved

Need to return the most recent sale for each salesman

Posted on 2008-10-21
4
252 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your help. This is exactly what I needed.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now