September 26, 2006 at 11:58 am
I have a data table (Table1) with 3 fields: Name, Date, Amount. There are multiple records for the same person in "Name". So, for example I have this data in Table1:
Name Date Amount
Smithy 1/1/1999 2000
Smithy 1/4/2001 199
Ann 1/2/2002 432
George 1/4/2002 5009
Ann 1/3/2003 3455
I want to know what the highest value is in "Amount" for each of the people in "Name" and return that record. So, for exmple, I would want the output like this:
Name Date Amount
Smithy 1/1/1999 2000
Ann 1/3/2003 3455
George 1/4/2002 5009
If anyone could help me with this I would greatly appreciate it.
Thanks
Will
September 26, 2006 at 12:56 pm
OK! I figured it out! I did this:
SELECT MAX([Table1].Amount), [Table1].Name
FROM [Table1]
GROUP BY [Table1].Name;
Will
September 26, 2006 at 1:56 pm
OK, that didn't work as I thought it did. What I want is to report back the whole record, not just the Name and Amount. I also want whatever Date is associated with the Amount.
Can anyone help?
Thanks
Will
September 27, 2006 at 12:57 am
Add the Date field to the Select statement and the Group By clause.
September 27, 2006 at 12:59 am
Hi,
One of the problems with total queries is displaying information that does not take part in the actual grouping. Use a two stage approach. use the query above in another query lining back to the original table using date and name to link the tables together. Then you can display whatever information you require from your main table.
September 27, 2006 at 12:59 am
Will, here is the SQL for the first query (Name it Query15 for this example):
SELECT Table4.Name, Max(Table4.Amount) AS Amount
FROM Table4
GROUP BY Table4.Name;
Then the second query uses the first query (Query15) to select (JOIN) the records that have the Max amount for each Name:
SELECT Table4.Name, Table4.myDate, Table4.Amount
FROM Table4 INNER JOIN Query15 ON (Table4.Amount = Query15.Amount) AND (Table4.Name = Query15.Name);
HTH,
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 27, 2006 at 1:14 am
select Table1.Name, Table1.Data, Table1.Amount
from Table1 inner join (
SELECT MAX([Table1].Amount) Amount, [Table1].Name
FROM Table1
GROUP BY [Table1].Name) a
on Table1.Name=a.Name and Table1.Amount=a.Amount
September 27, 2006 at 10:23 am
If you have more than one date that the user made the same Max purchase, you will get mulitple records in your result set for the same max amount (each with different dates).
You'll need to group by a last time to get the max (or min) date for that max amount to account for those.
Select b.Name, Max(b.Date), b.Amount
FROM
(select Table1.Name, Table1.Data, Table1.Amount
from Table1 inner join (
SELECT MAX([Table1].Amount) Amount, [Table1].Name
FROM Table1
GROUP BY [Table1].Name) a
on Table1.Name=a.Name and Table1.Amount=a.Amount) b
Group by b.Name, b.Amount
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply