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