#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0

    Trying to get my head around MAX()


    Hi All

    In the process of streamlining my reports.

    Currently i would use two separate queries to produce the below information. One query with just case number and the 2nd query with a sorted by DESC on the compile date then use a vlookup and it will give me the latest date and recommendation.

    Ive been looking to advance now over the next few weeks with mysql and have tried unsuccessfully with max()

    i have created a sqlfiddle to help explain (ahh wont let me link the last part of the link is "#!2/21e72/1")

    Code:
    CREATE TABLE reporting
    (
    case_number mediumint(7),
    compiled_date date,
    recommendation tinytext
    );
    
    INSERT INTO reporting (case_number,compiled_date,recommendation)
    VALUES 
    ('1000','2010-12-11','pending'),
    ('1000','2010-12-12','let'),
    ('1000','2010-12-13','sell'),
    ('1000','2012-12-14','let'),
    ('1001','2010-12-11','pending'),
    ('1001','2010-12-12','let'),
    ('1001','2013-12-13','sell'),
    ('1001','2010-12-14','pending'),
    ('1002','2010-12-11','pending'),
    ('1002','2010-12-12','pending'),
    ('1002','2010-12-13','sell'),
    ('1002','2012-12-14','sell');
    Code:
    SELECT
    case_number,
    max(compiled_date),
    recommendation
    From
    reporting
    group by
    case_number

    i know that another select statement needs to go on the max(compiled_date) but dont know how to insert it. Using the sqlfiddle i would expect the results to be: -

    Code:
    CASE_NUMBER	MAX(COMPILED_DATE)	RECOMMENDATION
    1000	                December, 14 2012       let
    1001	                December, 13 2013 	sell
    1002	                December, 14 2012 	sell
    not

    Code:
    CASE_NUMBER	MAX(COMPILED_DATE)	RECOMMENDATION
    1000	December, 14 2012 	pending
    1001	December, 13 2013	pending
    1002	December, 14 2012 	pending
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    This is a known misfeature in Mysql. The value for the recommendation column is arbitrarily chosen
    for each case_number. What you need to do is to make a join to the maximum compiled_date

    Code:
    select case_number,
           compiled_date,
           recommendation
      from reporting
      join (select case_number,
                   max(compiled_date) as maxDate
              from reporting
             group
                by case_number) dt
        on reporting.case_number = dt.case_number
       and reporting.compiled_date = dt.maxDate
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    i'm sure your understanding of MAX() is fine

    where you're falliung down, as so many others do, is understanding why mysql lets you get away with putting recommendation into your SELECT list and not in your GROUP BY clause

    for more background, see MySQL Extensions to GROUP BY

    in short, if all the values of recommendation for each case_number are the same, then your query would return the correct results

    but since they aren't, the value that is returned for recommendation is indeterminate

    you need to first isolate which row has the latest compiled date, and then pull that row from the table

    Code:
    SELECT t.case_number
         , t.compiled_date
         , t.recommendation
      FROM ( SELECT case_number
                  , MAX(compiled_date) AS latest
               FROM reporting
             GROUP 
                 BY case_number ) AS m
    INNER
      JOIN reporting AS t
        ON t.case_number = m.case_number
       AND t.compiled_date = m.latest
    edit: i see swamboogie beat me to it

    interesting choice of word... "misfeature"

    see Debunking GROUP BY myths

    once you understand why it works the way it does, you can use this "feature" to make GROUP BY queries more efficient

    Last edited by r937; January 7th, 2014 at 09:12 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    That's happening due to the recommendation column not participating in the GROUP BY and not being the target of an aggregate function so, in effect, a random value from the rows 'collapsed down' into the GROUP by is shown.

    You'll need to return the case_number and MAX() columns from a sub-query and do a 'self join' back to the original table using those returned columns as part of the ON clause.

    <edit>
    Gah, not only sniped, but sniped twice!!!!
    </edit>
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    ok that works in both instances

    thank you so much i thought my database was actually wrong!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Are you using the table alias names in all relevant places?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    thanks both

    i can finally do away with these silly vlookup\double queries
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    quick question so i can just confirm im fully understanding this as i dont want to be spoonfed the answer!

    FROM ( SELECT case_number
    , MAX(compiled_date) AS latest
    FROM reporting
    GROUP
    BY case_number ) AS m
    INNER
    JOIN reporting AS t
    ON t.case_number = AS mcase_number
    AND t.compiled_date = AS mlatest [/CODE]

    the bolded bits they are temporary tables holding data?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Yes, they are used to reference the result set specified by the derived table.

    The result set is not necessarily stored in a temporary table since it does not need to be materialized in all cases.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    trying to get a little more complicated now by using other tables (client and staff which link to a main table)

    Code:
    SELECT
    case_process.case_number,
    case_process.full_address,
    client.client_name,
    staff.SIGNED_NAME,
    t.compiled date,
    t.recommendation
    FROM
    (SELECT
    case_number,
    MAX(compiled_date) AS Latest,
    recommendation
    from 
    exit_strategy
    group BY
    case_number
     ) AS a
    INNER JOIN exit strategy AS T ON a.case_number = case_process.case_number AND t.compiled_date = a.latest
    INNER JOIN client ON client.client_number = case_process.client_number 
    INNER JOIN staff ON case_process.perm_staff_number = staff.STAFF_NUMBER
    WHERE
    case_process.case_active = 1
    getting an error around line 18?!?

    if its any help case_process table has only ever one case and there can only ever be one staff and one client attached to a case. im only complete max() on exit strategy.compile date with the recent recommendation

    truly sorry to be a pain!
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Your on clause has a reference to a table named case_process which is not mentioned in the from clause.

    Including the column recommendation in the select list for the derived table A is unnecessary.

IMN logo majestic logo threadwatch logo seochat tools logo