MaxDate and Group (1 Viewer)

[kenrav]

I have the following table (attached):

I'd like to create a query that displays the Max(OrderDate) details by Customer:

OrderDate Customer ItemNum SerialNum
10/3/2022 Andy 300 323456780
10/3/2022 Betty 900 923456789

I've read a few articles but can't get it to work.

Thanks!

 

[bob fitz]

Try:
SELECT TOP 1 Table1.OrderDate, Table1.Customer, Table1.ItemNum, Table1.SerialNum
FROM Table1
GROUP BY Table1.OrderDate, Table1.Customer, Table1.ItemNum, Table1.SerialNum
ORDER BY Table1.OrderDate DESC;

 

[kenrav]

Bob,

Thanks for your help. Your solution works perfectly based on my previous data.

Unfortunately, I forgot to mention that not all customers will have the same order dates. My bad. As such, the following set of records:

only returns the following:

Again, my mistake. What I really need is for it to return the last order for all each customer.

I'm pretty sure the solution involves Max(OrderDate). I did this several years ago but can't find my old code.

Again, thanks for helping me.

I've attached an updated set of data for anyone willing to help.

Thanks!

Ken

 

[Gasman]

I can get your requirements, but had to count the other fields as I used a Totals query?

SELECT Max(Table1.OrderDate) AS MaxOfOrderDate, Table1.Customer, Count(Table1.ItemNum) AS CountOfItemNum, Count(Table1.SerialNum) AS CountOfSerialNum
FROM Table1
GROUP BY Table1.Customer;

 

[MarkK]

I think you need a subquery (named qMax) to get your max dates first, like...

SELECT Max(Table1.OrderDate) AS MaxOfOrderDate, Table1.Customer
FROM Table1
GROUP BY Table1.Customer;

Then use that to return the qualifying rows....

SELECT Table1.OrderDate, Table1.Customer, Table1.ItemNum, Table1.SerialNum
FROM qMax INNER JOIN Table1 ON (qMax.Customer = Table1.Customer) AND (qMax.MaxOfOrderDate = Table1.OrderDate);

 

[XPS35]

Try

SELECT * FROM Table1 INNER JOIN 
(SELECT Table1.Customer, Max(Table1.OrderDate) AS MaxOrderDate FROM Table1 GROUP BY Table1.Customer)  AS Q1 
ON (Table1.Customer = Q1.Customer) AND (Table1.OrderDate = Q1.MaxOrderDate);

 

[kenrav]

Try

SELECT * FROM Table1 INNER JOIN
(SELECT Table1.Customer, Max(Table1.OrderDate) AS MaxOrderDate FROM Table1 GROUP BY Table1.Customer)  AS Q1
ON (Table1.Customer = Q1.Customer) AND (Table1.OrderDate = Q1.MaxOrderDate);

Thanks everyone for your help!

 

[Gasman]

Thanks everyone for your help! XPS35's solution was easiest to implement.

No different to MarkK's really, just in one step?
When you do not know what you are doing, breaking it down into small steps tends to get you there?

 

[kenrav]

No different to MarkK's really, just in one step?
When you do not know what you are doing, breaking it down into small steps tends to get you there?

You're absolutely correct.

 

[XPS35]

No different to MarkK's really, just in one step?
When you do not know what you are doing, breaking it down into small steps tends to get you there?

You are right. I missed MarkK's post.

You are also (partly) right about breaking down in small steps. That is how I build this query. First make two query's and then integrate them. In order to maintain an overview, one query is ultimately better.
If you don't know what you're doing, it doesn't matter which solution you use