mysql:  query to find the max within each grouping

Posted on 2013-11-11
Last Modified: 2013-11-11
i have the following table:    tblOrders   ORD   (primary key is OrderID)
with the following fields

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:
           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!
Question by:willsherwood
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 41

Assisted Solution

Sharath earned 500 total points
ID: 39640577
SELECT t1.OrderID, t1.CustID, t1.EndDate
  FROM tblOrders AS t1
                      FROM tblOrders
                    GROUP BY CustID) AS t2
  ON t1.CustID = t2.CustID AND t1.EndDate = t2.Max_EndDate
LVL 41

Accepted Solution

Sharath earned 500 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)

Author Closing Comment

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

Author Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MSSQL join different row from other table 14 84
Estimating my database size 7 51
SQL Query Works in SQL 2008 & 2012 But Not SQL 2016 15 101
SQL Query 20 25
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

710 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