So I have these 2 tables on Oracle:
CLIENT
cl_id cl_name
1 John
2 Maria
PAYMENTS
pa_id pa_date pa_status cl_id
1 2017-01-01 1 1
2 2017-01-01 1 2
3 2017-02-01 1 1
4 2017-02-01 1 2
5 2017-03-01 0 1
6 2017-03-01 1 2
I need a select statemant that gives me the client ID, NAME and the STATUS of his last payment. So the end result of my select should be:
cl_id cl_name pa_status
1 John 0
2 Maria 1
This is the CLIENT select that works:
select cl_id, cl_name from CLIENT;
This is the last status of the PAYMENT select that works:
select * from (
select pa_status from PAYMENT ORDER BY PA_DATE DESC)
where rownum = 1;
So now, I need to make them work together. I tried 2 ways that didn't work:
select cl_id, cl_name, (select * from (
select pa_status from PAYMENT ORDER BY PA_DATE DESC)
where rownum = 1 and PAYMENT.cl_id = CLIENT.CL_ID) as last_status from CLIENT;
error: invalid identifier
AND this:
select cl_id, cl_name, (select * from (
select pa_status from PAYMENT ORDER BY PA_DATE DESC)
where rownum = 1 ) as last_status from CLIENT;
which don't give me any errors, but only shows the same last status of John that is the last record:
cl_id cl_name last_status
1 John 0
2 Maria 0
Can anyone give me a hint?
Thanks
you need to use analystic function. This kind of functions let you split your data to some groups, and rank the data for each group as you wish.
In your case:
Select * from (
Select id, name, status, row_number () over (partition by p.cl_id order by p.pa_date desc) as rw
From client c join payments p on p.cl_id = c.cl_id)
Inn where inn.rw = 1;