CODEWITHSUNDEEP
sqlinner-join
Marcos
Marcos

Reputation: 756

Inner join same table syntax error

I'm trying to run an inner join between this same table. What is wrong with my syntax?

(SELECT user_key, bill_number, MAX(payment_date) AS payment_date 
 FROM billpayment) bpt 
 INNER JOIN (SELECT * FROM billpayment) bp
   ON bp.user_key=bpt.user_key 
   AND bp.bill_number=bpt.bill_number 
   AND bp.payment_date=bpt.payment_date 
 GROUP BY user_key, bill_number;

It says that the sql command is not properly ended at the parenthesis in 'billpayment)'

Upvotes: 1

Views: 674

Answers (4)

AvrahamL
AvrahamL

Reputation: 201

On mySQL, you can try something like:

 SELECT bpt.user_key, bpt.bill_number, MAX(bpt.payment_date) AS payment_date 
 FROM billpayment bpt 
 INNER JOIN (SELECT * FROM billpayment) bp
   ON bp.user_key=bpt.user_key 
   AND bp.bill_number=bpt.bill_number 
   AND bp.payment_date=bpt.payment_date 
 GROUP BY bpt.user_key, bpt.bill_number;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

How about just using analytic functions?

select bp.*,
       max(bp.paymentdate) over (partition by user_key, bill_number) as max_payment_date
from billpayment

If you want the fields on the max payment date:

select bp.*
from (select bp.*,
             max(bp.paymentdate) over (partition by user_key, bill_number) as max_paymentdate
      from billpayment
     ) bp
where paymentdate = max_paymentdate;

Upvotes: 0

Akio Hamasaki
Akio Hamasaki

Reputation: 535

On Oracle Try this:

  SELECT *
    FROM (SELECT user_key, bill_number, MAX (payment_date) AS payment_date FROM billpayment) bpt
         INNER JOIN billpayment bp
            ON bp.user_key = bpt.user_key AND bp.bill_number = bpt.bill_number AND bp.payment_date = bpt.payment_date
    GROUP BY user_key, bill_number;

Can't test it without the table structure.

Upvotes: 1

kmsj13
kmsj13

Reputation: 28

SELECT 
     user_key, 
     bill_number, 
     MAX(payment_date) AS payment_date 
    FROM billpayment bpt 
    INNER JOIN billpayment bp 
    ON bp.user_key=bpt.user_key 
    AND bp.bill_number=bpt.bill_number AND bp.payment_date=bpt.payment_date 
    GROUP BY 
     user_key, bill_number

;

Upvotes: 2

Related Questions