By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Select first row in multi group query

P: 9
Pardon me if this question already been asked before.

I tried to sort a query to meet my desire. Here is the example:
Expand|Select|Wrap|Line Numbers
  1. Date       Customer  Supplier   Price
  2. 31-Oct-09  Charlie   Australia  $ 100
  3. 20-Oct-09  Omega     Singapore   $ 50
  4. 4-Oct-09   Charlie   Australia   $ 20
  5. 5-Oct-09   Omega     Singapore   $ 60
  6. 15-Oct-09  Charlie   China      $ 200
  7. 20-Oct-09  Charlie   China      $ 300
My goal is to get latest price for each customer and supplier. Which the result should be:
Expand|Select|Wrap|Line Numbers
  1.   Date     Customer  Supplier   Price
  2. 31-Oct-09  Charlie   Australia  $ 100
  3. 20-Oct-09  Omega     Singapore   $ 50
  4. 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
Oct 19 '09 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(Customer.Date) AS MaxOfDate, Customer.Customer, Customer.Supplier
  2. FROM Customer
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT qryMaxCustomer.MaxOfDate, qryMaxCustomer.Customer, qryMaxCustomer.Supplier, Customer.Price
  2. 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

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 266
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(Customer.Date) AS MaxOfDate, Customer.Customer, Customer.Supplier
  2. FROM Customer
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT qryMaxCustomer.MaxOfDate, qryMaxCustomer.Customer, qryMaxCustomer.Supplier, Customer.Price
  2. 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
Oct 19 '09 #2

NeoPa
Expert Mod 15k+
P: 31,271
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Max([Date]) AS MaxDate,
  2.          [Customer],
  3.          [Supplier],
  4.          Val(Mid(Max(Format([Date],'yyyymmddHHnnss') &
  5.                      [Price]),15)) AS LastPrice
  6.  
  7. FROM     [SomeTable]
  8.  
  9. GROUP BY [Customer],
  10.          [Supplier]
Oct 19 '09 #3

P: 9
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
Oct 20 '09 #4

NeoPa
Expert Mod 15k+
P: 31,271
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.
Oct 20 '09 #5

NeoPa
Expert Mod 15k+
P: 31,271
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).

Expand|Select|Wrap|Line Numbers
  1. SELECT tC.Customer,
  2.        tC.Supplier,
  3.        tC.Date,
  4.        tC.Price
  5.  
  6. FROM   Customer AS tC INNER JOIN
  7.     (
  8.     SELECT   [Customer],
  9.              [Supplier],
  10.              Max([Date]) AS MaxDate
  11.  
  12.     FROM     [Customer]
  13.  
  14.     GROUP BY [Customer],
  15.              [Supplier]
  16.     ) AS subQ
  17.   ON   (tC.Customer=subQ.Customer)
  18.  AND   (tC.Supplier=subQ.Supplier)
  19.  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).
Oct 20 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.