Select first row in multi group query

[eko99312]

Pardon me if this question already been asked before.

I tried to sort a query to meet my desire. Here is the example:

[I]Date       Customer  Supplier   Price[/I]
31-Oct-09  Charlie   Australia  $ 100
20-Oct-09  Omega     Singapore   $ 50
4-Oct-09   Charlie   Australia   $ 20
5-Oct-09   Omega     Singapore   $ 60
15-Oct-09  Charlie   China      $ 200
20-Oct-09  Charlie   China      $ 300

My goal is to get latest price for each customer and supplier. Which the result should be:

  [I]Date     Customer  Supplier   Price[/I]
31-Oct-09  Charlie   Australia  $ 100
20-Oct-09  Omega     Singapore   $ 50
20-Oct-09  Charlie   China      $ 300

I tried to descending the date order and set Last on Date and Price at the Total row. But it doesn't effect a thing.

Please help me on this case.
Regards,
Eko/Indonesia

[ajalwaysus]

This is not one of my stronger areas, but I do know of one way of getting the results you desire. It will require 2 queries.

1. Assuming the data is stored in a table called "Customer", this is the first query:

SELECT Max(Customer.Date) AS MaxOfDate, Customer.Customer, Customer.Supplier
FROM Customer
GROUP BY Customer.Customer, Customer.Supplier;

2. Assuming the first query is named "qryMaxCustomer ", this is the second query, which should return the data you desire:

SELECT qryMaxCustomer.MaxOfDate, qryMaxCustomer.Customer, qryMaxCustomer.Supplier, Customer.Price
FROM Customer INNER JOIN qryMaxCustomer ON (Customer.Supplier = qryMaxCustomer.Supplier) AND (Customer.Customer = qryMaxCustomer.Customer) AND (Customer.Date = qryMaxCustomer.MaxOfDate);

Let me know if you have any questions.

-AJ

[NeoPa]

For that sort of query I normally use a technique where I join the fields together in a string; find the maximum; then extract the data I'm interested in from the resultant string and convert it if required.

In this case that would translate to :

SELECT   Max([Date]) AS MaxDate,
         [Customer],
         [Supplier],
         Val(Mid(Max(Format([Date],'yyyymmddHHnnss') &
                     [Price]),15)) AS LastPrice

FROM     [SomeTable]

GROUP BY [Customer],
         [Supplier]

[eko99312]

Dear AJ,

Thank you so much for the answer.
Since I don't understand SQL, I tried to apply your method by using query design view, and it works very well. Wow using 2 queries, why didn't I think of that.

And for NeoPa, I appreciate your answer and I'll learn more about your method.

Regards,
Eko

[NeoPa]

SQL is fun. Remember though, if you're still quite new to it, that every QueryDef (saved query in Access) has SQL associated with it. Simply select View / SQL View and View / Design View to switch between two different, but matching, views of the same QueryDef. Each view allows design changes to it. This means you can always Copy the SQL of any QueryDef to be posted for instance. It also means that SQL already copied can be Pasted into the SQL View of a query to change it completely to reflect that new SQL. Very powerful concept.

[NeoPa]

Another point to ponder is that AJ's solution can be combined into a single query if you use a subquery (See Subqueries in SQL).

SELECT tC.Customer,
       tC.Supplier,
       tC.Date,
       tC.Price

FROM   Customer AS tC INNER JOIN
    (
    SELECT   [Customer],
             [Supplier],
             Max([Date]) AS MaxDate

    FROM     [Customer]

    GROUP BY [Customer],
             [Supplier]
    ) AS subQ
  ON   (tC.Customer=subQ.Customer)
 AND   (tC.Supplier=subQ.Supplier)
 AND   (tC.Date=subQ.MaxDate)

PS. I'm very glad AJ posted this technique as I've been using the other for so long I forgot about this (more standard/typical) one. I prefer my one for one or two fields, but when there are a number of them then this method wins hands down (& I forgot to use it recently - Kicks self).