Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Unanswered: I am receiving multiple rows from an SQL SELECT when using a MAX function on a date,

    I am receiving multiple rows from an SQL SELECT when using a MAX function on a date, which seems counter-intuitive.
    How can I code this to narrow the results down to the most recent date for the unique combination of columns A-F? I want all the data from only rows 2 and 4 returned, since they reflect the MAX date for all these columns of 2011-04-01. Can anyone shed some light as to why duplicates are returned despite the fact that MAX is coded?

    Do I need to code using DISTINCT on columns A-F, since the price in column G is different? If that would work, how would that be coded? Or is the answer in coding a Sub-SELECT?
    Thanks for any assistance you can provide.

    SQL statement is:

    SELECT

    CDE_ITEM AS A,
    IDT_MKTG_PGM AS B,
    DTE_MKTG_PGM_EFF AS C,
    MAX(DTE_PRICE_EFF) AS D,
    CDE_CUST_TYPE AS E,
    CDE_ITEM_COND AS F,
    AMT_LIST_PRICE AS G (not part of an index)


    FROM PDB2V.TPPRSP1
    WHERE

    IDT_MKTG_PGM = 'STANDARD'
    AND
    CDE_ITEM = ‘XXXX’
    AND
    YEAR(DTE_PRC_LAST_MAINT) BETWEEN 2010 AND 2012 --(not part of index)

    GROUP BY

    CDE_ITEM, -- A
    IDT_MKTG_PGM, --B
    DTE_MKTG_PGM_EFF, --C
    CDE_CUST_TYPE, --E
    CDE_ITEM_COND, --F
    AMT_LIST_PRICE --G

    ORDER BY

    CDE_ITEM, -- A
    IDT_MKTG_PGM, --B
    DTE_MKTG_PGM_EFF, --C
    CDE_CUST_TYPE, --E
    CDE_ITEM_COND, --F
    AMT_LIST_PRICE ; --G


    UNIQUE CLUSTERED INDEX (table’s only index) is defines as follows:
    CREATE UNIQUE INDEX PDB2V.XPPRSP1
    ON PDB2V.TPPRSP1
    (CDE_ITEM ASC, -- A
    IDT_MKTG_PGM ASC, -- B
    DTE_MKTG_PGM_EFF DESC, --C
    DTE_PRICE_EFF DESC, --D
    CDE_CUST_TYPE ASC, --E
    CDE_ITEM_COND ASC) --F
    USING VCAT PDB2V
    CLUSTER
    Etc…

    RESULTS FROM QUERY:

    +------------------------------------------------------------------------
    | A | B | C | D | E | F | G
    +------------------------------------------------------------------------
    1_| XXXX | STANDARD | 1999-12-31 | 2011-03-01 | C | N | 3495.00
    2_| XXXX | STANDARD | 1999-12-31 | 2011-04-01 | C | N | 3995.00
    3_| XXXX | STANDARD | 1999-12-31 | 2011-03-01 | G | N | 2935.00
    4_| XXXX | STANDARD | 1999-12-31 | 2011-04-01 | G | N | 3355.00
    +------------------------------------------------------------------------

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If there are multiple rows that have the same data value for that date (which happens to be max) why would they Not all be selected?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try the following examples.

    I'll recommend Example 1.
    Because, it doesn't include duplicate WHERE conditions and execution performance may be better.

    Example 1:
    Code:
    SELECT
           CDE_ITEM         AS A
         , IDT_MKTG_PGM     AS B
         , DTE_MKTG_PGM_EFF AS C
         , DTE_PRICE_EFF    AS D
         , CDE_CUST_TYPE    AS E
         , CDE_ITEM_COND    AS F
         , AMT_LIST_PRICE   AS G
     FROM  (SELECT t.*
                 , RANK()
                      OVER( ORDER BY DTE_PRICE_EFF DESC ) AS rank_eff
             FROM  PDB2V.TPPRSP1 t
             WHERE CDE_ITEM     = 'XXXX'
               AND IDT_MKTG_PGM = 'STANDARD'
    --           AND YEAR(DTE_PRC_LAST_MAINT) BETWEEN 2010 AND 2012
               AND DTE_PRC_LAST_MAINT BETWEEN '2010-01-01' AND '2012-12-31'
           ) s
     WHERE rank_eff = 1
     ORDER BY
           A , B , C , E , F , G

    Example 2:
    Code:
    SELECT
           CDE_ITEM         AS A
         , IDT_MKTG_PGM     AS B
         , DTE_MKTG_PGM_EFF AS C
         , DTE_PRICE_EFF    AS D
         , CDE_CUST_TYPE    AS E
         , CDE_ITEM_COND    AS F
         , AMT_LIST_PRICE   AS G
     FROM  PDB2V.TPPRSP1
     WHERE CDE_ITEM     = 'XXXX'
       AND IDT_MKTG_PGM = 'STANDARD'
    --   AND YEAR(DTE_PRC_LAST_MAINT) BETWEEN 2010 AND 2012
       AND DTE_PRC_LAST_MAINT BETWEEN '2010-01-01' AND '2012-12-31'
       AND DTE_PRICE_EFF
           = (SELECT MAX(DTE_PRICE_EFF)
               FROM  PDB2V.TPPRSP1
               WHERE CDE_ITEM     = 'XXXX'
                 AND IDT_MKTG_PGM = 'STANDARD'
    --             AND YEAR(DTE_PRC_LAST_MAINT) BETWEEN 2010 AND 2012
                 AND DTE_PRC_LAST_MAINT BETWEEN '2010-01-01' AND '2012-12-31'
             )
     ORDER BY
           A , B , C , E , F , G
    This index may be effective for both queries.
    Code:
    CREATE INDEX PDB2V.X2_PPRSP1
    ON PDB2V.TPPRSP1
    ( CDE_ITEM
    , IDT_MKTG_PGM
    , DTE_PRC_LAST_MAINT
    , DTE_PRICE_EFF
    -- additional columns
    )
    -- options
    ;

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Possibly i'm lost (again<g>) . . .

    If there are multiple rows that have the same data value for that date (which happens to be max) why would one be wanted and not the others? How is one cde_item a "better" choice than the others?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there are multiple rows that have the same data value for that date (which happens to be max) ...
    I think all rows having MAX(DTE_PRICE_EFF) be wanted. Not choose one.

    ... I want all the data from only rows 2 and 4 returned, since they reflect the MAX date for all these columns of 2011-04-01. ...
    RESULTS FROM QUERY:

    +------------------------------------------------------------------------
    | A | B | C | D | E | F | G
    +------------------------------------------------------------------------
    1_| XXXX | STANDARD | 1999-12-31 | 2011-03-01 | C | N | 3495.00
    2_| XXXX | STANDARD | 1999-12-31 | 2011-04-01 | C | N | 3995.00
    3_| XXXX | STANDARD | 1999-12-31 | 2011-03-01 | G | N | 2935.00
    4_| XXXX | STANDARD | 1999-12-31 | 2011-04-01 | G | N | 3355.00
    +------------------------------------------------------------------------

  6. #6
    Join Date
    Aug 2012
    Posts
    3

    Response to papadi

    I want the rows having the latest dates for all the different combinations of columns A-F, where in this case column E is the decision maker. I need the latest price when the customer type code is C, G, or whatever values of "CDE_CUST_TYPE AS E" are. Value for column G is not part of the decision criteria, but part of the data that is returned. Does that clarify it?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm confusing a little now.

    I want the rows having the latest dates for all the different combinations of columns A-F
    But, your query specified values for A and B in WHERE clause,
    so only differences may be in C, E, F, because you want MAX(D).

    And your sample data didn't include different C and F.

    If some MAX(DTE_PRICE_EFF) /*i.e. D*/ were different for each CDE_CUST_TYPE /*i.e. E*/, like ...
    Code:
    A    B        C          D          E F G
    ---- -------- ---------- ---------- - - -------
    XXXX STANDARD 1999-12-31 2011-03-01 C N 3495.00
    XXXX STANDARD 1999-12-31 2011-04-01 C N 3995.00
    XXXX STANDARD 1999-12-31 2011-03-01 G N 2935.00
    XXXX STANDARD 1999-12-31 2011-04-01 G N 3355.00
    XXXX STANDARD 1999-12-31 2011-05-01 G N 3055.00
    What rows do you want?

    Anyway,
    PLEASE supply more sample data and expected result from the data,
    which include most possible patterns and show your requirements fully.
    Last edited by tonkuma; 08-19-12 at 12:05.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that from
    Code:
    A    B        C          D          E F G
    ---- -------- ---------- ---------- - - -------
    XXXX STANDARD 1999-12-31 2011-03-01 C N 3495.00
    XXXX STANDARD 1999-12-31 2011-04-01 C N 3995.00
    XXXX STANDARD 1999-12-31 2011-03-01 G N 2935.00
    XXXX STANDARD 1999-12-31 2011-04-01 G N 3355.00
    XXXX STANDARD 1999-12-31 2011-05-01 G N 3055.00
    you might want two rows
    Code:
    A    B        C          D          E F G
    ---- -------- ---------- ---------- - - -------
    XXXX STANDARD 1999-12-31 2011-04-01 C N 3995.00
    XXXX STANDARD 1999-12-31 2011-05-01 G N 3055.00
    If my guess was right,
    try by adding PARTITION BY clause for Example 1, or addidng some conditions for a subquery in Exsample 2, like

    Example 1a:
    Code:
    ...
                      OVER( PARTITION BY DTE_MKTG_PGM_EFF
                                       , CDE_CUST_TYPE
                                       , CDE_ITEM_COND
                                ORDER BY DTE_PRICE_EFF DESC ) AS rank_eff
    ...
    Example 2a:
    Code:
    ...
     FROM  PDB2V.TPPRSP1 t
     WHERE CDE_ITEM     = 'XXXX'
       AND IDT_MKTG_PGM = 'STANDARD'
       AND DTE_PRC_LAST_MAINT BETWEEN '2010-01-01' AND '2012-12-31'
       AND DTE_PRICE_EFF
           = (SELECT MAX(s.DTE_PRICE_EFF)
               FROM  PDB2V.TPPRSP1 s
               WHERE s.CDE_ITEM     = 'XXXX'
                 AND s.IDT_MKTG_PGM = 'STANDARD'
                 AND s.DTE_PRC_LAST_MAINT BETWEEN '2010-01-01' AND '2012-12-31'
                 AND s.DTE_MKTG_PGM_EFF = t.DTE_MKTG_PGM_EFF
                 AND s.CDE_CUST_TYPE    = t.CDE_CUST_TYPE
                 AND s.CDE_ITEM_COND    = t.CDE_ITEM_COND
             )
    ...

  9. #9
    Join Date
    Aug 2012
    Posts
    3
    Yes, you are correct. For the unique combination (aka concatenation aka compound key values) of columns A, B, C, E, F -together-, for its latest price effective date (MAX(DTE_PRICE_EFF) AS D) I want to display its price in G (along with other non-key columns I did not mention in this example, such as H, I, J, etc.).

    We are still in Version 8 NFM until early next year, and on z/OS V1.11 until November, when we migrate to z/OS V1.13, so I don't believe I can use RANK at this time.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But, you may be able to use Example 2a on DB2 8 for z/OS.

Posting Permissions

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