Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Answered: Issues with max value of a field

    Good afternoon all!

    New SQL guy here looking to find the most recent date a customer was amended (AMDATE). The below query shows correct values if a customer was never amended....those that were amended naturally have multiple records.
    Here's what I got so far:

    Select
    WHS,
    CUSNO,
    AMDATE As "Max_AMDATE"
    From MBC6REVQ
    Group By WHS, CUSNO, AMDATE
    Order By WHS, "Max_C6AMDT" Desc


    I'm only looking to retrieve the latest (Max_AMDATE) per warehouse.

    I tried to create a subquery:

    Select
    WHS,
    Max(AMDATE) As Max_AMDATE,
    Max(Distinct (Select
    Max(AMDATE)
    From
    MBC6REVQ
    Group By
    WHS
    Fetch First
    1 Rows Only)) As MAX_MOD,
    AMDATE
    From
    MBC6REVQ
    Group By
    WHS, AMDATE
    Order By
    WHS, AMDATE Desc,
    MAX_MOD Desc


    ..which gives the same results as the first query. Please tell me what I am doing wrong!!! Any assistance is greatly appreciated!!!

    Thanks in advance!

    Matt

  2. Best Answer
    Posted by aflorin27

    "Why are you
    Group By WHS, CUSNO, AMDATE ?

    If you want the most recent date per warehouse and customer, just Group By WHS, CUSNO"


  3. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Why are you
    Group By WHS, CUSNO, AMDATE ?

    If you want the most recent date per warehouse and customer, just Group By WHS, CUSNO
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #3
    Join Date
    Jan 2016
    Posts
    2

    D'oh!

    Quote Originally Posted by aflorin27 View Post
    Why are you
    Group By WHS, CUSNO, AMDATE ?

    If you want the most recent date per warehouse and customer, just Group By WHS, CUSNO
    Yeah, you're right. I feel like an idiot now!

    Much thanks, aflorin27!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •