willsherwood
asked on
mysql: query to find the max within each grouping
i have the following table: tblOrders ORD (primary key is OrderID)
with the following fields
OrderID
CustID
EndDate
I want a query that returns the above three fields WHERE the ORD records have the max of each customer's EndDates to be
in a given range (the goal is to evaluate renewal reminder lists)
here's an example of 4 order records (abstracted for discussion)
OrderID CustID EndDate
1 100 2011
2 100 2012
3 200 2012
4 200 2013
something like:
SELECT OrderID, CustID, EndDate FROM ORD
WHERE ( MAX(EndDate) withinEach custid ) <= 2012
(and the desired result would be OrderID=2 record)
i'm not sure if GROUP BY(MAX) is the right strategy, i don't think so, since i can't find how to group by CustID and then find a different field's max(EndDate)
thanks for any help!
with the following fields
OrderID
CustID
EndDate
I want a query that returns the above three fields WHERE the ORD records have the max of each customer's EndDates to be
in a given range (the goal is to evaluate renewal reminder lists)
here's an example of 4 order records (abstracted for discussion)
OrderID CustID EndDate
1 100 2011
2 100 2012
3 200 2012
4 200 2013
something like:
SELECT OrderID, CustID, EndDate FROM ORD
WHERE ( MAX(EndDate) withinEach custid ) <= 2012
(and the desired result would be OrderID=2 record)
i'm not sure if GROUP BY(MAX) is the right strategy, i don't think so, since i can't find how to group by CustID and then find a different field's max(EndDate)
thanks for any help!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
follow up:
how to extend your solution to do the nested inner select in your second example,
to use its value twice if i need the EndDate to be in a range?
date1 < MaxEndDate < date2
i.e., date1 < (sub select...) AND date2 > (sub select...)
without repeating the inner select (it's not a huge DB and this is done infrequently, so it's not earth shattering to repeat it)
i'm not sure where the "external" variable "2012" (in my original example) comes into play
how to extend your solution to do the nested inner select in your second example,
to use its value twice if i need the EndDate to be in a range?
date1 < MaxEndDate < date2
i.e., date1 < (sub select...) AND date2 > (sub select...)
without repeating the inner select (it's not a huge DB and this is done infrequently, so it's not earth shattering to repeat it)
i'm not sure where the "external" variable "2012" (in my original example) comes into play
ASKER
love the nested SELECT approach.