?
Solved

mysql:  query to find the max within each grouping

Posted on 2013-11-11
4
Medium Priority
?
255 Views
Last Modified: 2013-11-11
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!
0
Comment
Question by:willsherwood
  • 2
  • 2
4 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 2000 total points
ID: 39640577
SELECT t1.OrderID, t1.CustID, t1.EndDate
  FROM tblOrders AS t1
 INNER JOIN (SELECT CustID,MAX(EnDDate) AS Max_EndDate
                      FROM tblOrders
                    GROUP BY CustID) AS t2
  ON t1.CustID = t2.CustID AND t1.EndDate = t2.Max_EndDate
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39640583
SELECT t1.OrderID, t1.CustID, t1.EndDate
  FROM tblOrders AS t1
 WHERE t1.EndDate =  (SELECT MAX(t2.EndDate)
                                      FROM tblOrders AS t2
                                    WHERE t1.CustID = t2.CustID)
0
 

Author Closing Comment

by:willsherwood
ID: 39640597
appreciate the quick reply.  MANY thanks!
love the nested SELECT approach.
0
 

Author Comment

by:willsherwood
ID: 39640602
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question