HELP - Retrieve last payment date in table wrong output

[rlbSQL]

Here is my SQL I am getting the information but should only see 1 record per vendor ID I only want to see the max date.

What am I missing to reduce the mulitple lines for this output.
Thanks.

Example:
Vendor ID 12 with date of 22-DEC-06.

SQL> select hdr.vendor_id, hdr.vendor_INVOICE_ID, max(pmt.pay_date) AS LAST_PAYMENT_DATE
  2  FROM mil_vendor_invoice_payment_mt pmt RIGHT OUTER JOIN  mil_vendor_invoice_mt hdr
  3  ON hdr.vendor_invoice_ID = pmt.vendor_INVOICE_ID
  4  GROUP BY hdr.vendor_id, hdr.vendor_invoice_ID;
 
 VENDOR_ID VENDOR_INVOICE_ID LAST_PAYM
---------- ----------------- ---------
        10                 4
        10                 5
        12                 1 20-DEC-06
        12                 2 22-DEC-06
        12                 3
        45                 6
        45                 7
 

[ronverdonk]

This one is different from your previous post. Now you are using 2 tables instead of 1 only.

So show us the table descriptions. AND: is the actual content of your date field '22-DEC-2006', i.e. is it a char type field and not a date or datetime type field?

Ronald :cool:

[rlbSQL]

This one is different from your previous post. Now you are using 2 tables instead of 1 only.

So show us the table descriptions. AND: is the actual content of your date field '22-DEC-2006', i.e. is it a char type field and not a date or datetime type field?

Ronald :cool:

I just got it Ronald.
I needed to set my group by to the vendor_id and it worked. I am new to using SQL and have been assigned some reports to do that will take some thinking so I will probably be coming back for questions.

Thank You for responding so quickly.

[ronverdonk]

You are welcome.

Ronald :cool: