ID number field for max date of customer

[kpereira]

Bear with me on this. I am having a brain freeze and can't move forward with this one. I have a table something like this:

IDField SaleDate CustomerID
1 1/1/08 552
2 4/3/07 552
3 12/1/07 552
4 4/3/08 444
5 6/3/07 444
6 2/4/08 444

I need to produce a query to find the IDField number for the latest SaleDate of a customer. In the above sample I would want the result to be:

1
4

So customer number 552 had the last sale date on 1/1/08 and customer 444 had his last sale on 4/3/08.

Obviously something like select max(SaleDate) isn't the option. Should I use an IN statement?

Help@!!!!!

 

[jbenson001]

Try:

select A.IDField
from Table A
INNER JOIN
   (Select CustomerID, Max(SaleDate) as MaxDate
    from Table
    group by CustomerID
) B on A.IDField = B.IDField AND
  A.SaleDate = B.MaxDate

 

[NuJoizey]

i'm having the same exact problem. I set up a test table as per kpereira's post, but I can't seem to get jbenson's code to work right.

This part of the code yields records 1 and 4 as above, only it does not show the IDField:

SELECT     CustomerID, MAX(SaleDate) AS MaxDate
FROM         tblA AS B
GROUP BY CustomerID

The question remains, how does one factor in the IDField? Everything I try just ends up showing the entire recordset again. I've tried using derived tables to no avail.

There must be a way, and it's probably using derived tables, but what is the concept I am missing here?

 

[NuJoizey]

here's something that seems to work:

SELECT     IDField, SaleDate, CustomerID
FROM         tblA AS T
WHERE     (SaleDate IN
                          (SELECT     MAX(SaleDate) AS MaxDate
                            FROM          tblA AS T1
                            GROUP BY CustomerID))

 

[davejam]

this is an old one, is it still active??

i would take the approach of the select subquery and not using MAX (i'm not a fan of functions sometimes when the code is just as easy!!!)

SELECT DISTINCT CustomerID,
(SELECT TOP 1 B.SaleDate
FROM dbo.tblA AS B
WHERE (A.CustomerID= B.CustomerID)
ORDER BY SaleDate DESC) AS LatestDate
FROM dbo.tblA AS A

Haven't tested, not sure if its better or just different!!!

cheers

daveJam

even my shrink says its all your f#@/ing fault

 

[NuJoizey]

yes, it's an old one, but still relevant! Fortunately, I ran across this whilst googling.

Your approach does seem to work fine. Using top 1 and a SELECT statement within in the SELECT clause

Thank you for posting it.