Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2017
    Posts
    1

    SQL For MAX Value on Distinct Items

    *** Update - Solved Below ***


    First time post. I'm 30 year UNIX veteran with no SQL skills, but I need to learn. I do that best by examples, so if anyone could help guide me on this new quest, I would very much appreciate you.

    I have the following tables...

    db2 => SELECT * FROM CUST

    CUSTOMER ITEM_NO ITEM_COST EFFECT_DATE TERM_DATE
    -------------------- ------- ---------- ----------- ----------
    Brian 1 40.00 010116 030117
    Bernie 1 27.50 020116 061517
    Joseph 2 100.00 030116 090117
    Jim 2 101.00 010117 010118


    db2 => SELECT * FROM ITEM

    ITEM_NO PRODUCT
    ------- ------------------------------
    1 200 Count Advil Gel Caplets
    2 500 Count Latex Gloves (powder

    2 record(s) selected.


    I need to display the CUSTOMER that is paying the highest ITEM_COST for the same item number (ITEM_NO). I have tried to use the DISTINCT and MAX but can't seem to get it right.

    I started with this to get my join which I'm happy with, but does not solve my issue.

    db2 => SELECT CUST.CUSTOMER, CUST.ITEM_NO, ITEM.PRODUCT, CUST.ITEM_COST FROM CUST JOIN ITEM ON

    CUST.ITEM_NO=ITEM.ITEM_NO

    CUSTOMER ITEM_NO PRODUCT ITEM_COST
    -------------------- ------- ------------------------------ ----------
    Brian 1 200 Count Advil Gel Caplets 40.00
    Bernie 1 200 Count Advil Gel Caplets 27.50
    Joseph 2 500 Count Latex Gloves (powder 100.00
    Jim 2 500 Count Latex Gloves (powder 101.00

    4 record(s) selected.



    I can show the MAX ITEM_COST of all 4 rows, but not sure how to just display the highest ITEM_COST for each ITEM_NO.

    Thank you,
    Brian





    Solution

    SELECT *
    FROM cust
    WHERE (cust.item_no, cust.item_cost) IN (
    SELECT cust.item_no
    ,MAX(cust.item_cost) AS max_item_cost
    FROM cust
    INNER JOIN
    item
    ON cust.item_no = item.item_no
    GROUP BY cust.item_no
    );
    Last edited by bkpitts01; 01-27-17 at 16:02. Reason: Solved

Posting Permissions

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