riffrack
asked on
Oracle GROUP BY question
Hi there
I'm currently having trouble with group by clause. Here is my table tbl_History
Partner_ID Paid Date Report_ID
1 40 01.01.2007 1
1 40 01.02.2007 1
1 40 01.03.2007 1
1 30 01.01.2007 2
1 30 01.02.2007 2
1 30 01.03.2007 2
So my SQL is:
SELECT Partner_ID, Paid, Date, MAX(Report_ID)
FROM tbl_History
GROUP BY Partner_ID, Paid, Date
Now, I would like to have only the Values which have the MAX(Report_ID), in this case that would be 2. But it returns all the values, becuase the Paid field is in the GROUP BY clause and has a different value.
How can get the SQL to show me the Paid field without it showing me too many results?
I'm currently having trouble with group by clause. Here is my table tbl_History
Partner_ID Paid Date Report_ID
1 40 01.01.2007 1
1 40 01.02.2007 1
1 40 01.03.2007 1
1 30 01.01.2007 2
1 30 01.02.2007 2
1 30 01.03.2007 2
So my SQL is:
SELECT Partner_ID, Paid, Date, MAX(Report_ID)
FROM tbl_History
GROUP BY Partner_ID, Paid, Date
Now, I would like to have only the Values which have the MAX(Report_ID), in this case that would be 2. But it returns all the values, becuase the Paid field is in the GROUP BY clause and has a different value.
How can get the SQL to show me the Paid field without it showing me too many results?
Is this what you are looking for?
select Partner_ID, Paid, dt
from(
SELECT Partner_ID, Paid, dt, report_ID, MAX(Report_ID) over() mr
FROM tbl_History )
WHERE report_id = mr
/
select Partner_ID, Paid, dt
from(
SELECT Partner_ID, Paid, dt, report_ID, MAX(Report_ID) over() mr
FROM tbl_History )
WHERE report_id = mr
/
SELECT Partner_ID, Paid, Date, MAX(Report_ID)
FROM tbl_History WHERE Report_ID = (select max(Report_ID) FROM tbl_History)
GROUP BY Partner_ID, Paid, Date
FROM tbl_History WHERE Report_ID = (select max(Report_ID) FROM tbl_History)
GROUP BY Partner_ID, Paid, Date
ASKER
hi there
thanks for the quick responses, but unfortunatly none of the suggestions seem to work.
the table will continue like this:
Partner_ID Paid Date Report_ID
1 40 01.01.2007 1
1 40 01.02.2007 1
1 40 01.03.2007 1
1 30 01.01.2007 2
1 30 01.02.2007 2
1 30 01.03.2007 2
2 25 01.01.2007 3
2 25 01.02.2007 3
2 25 01.03.2007 3
2 60 01.01.2007 4
2 60 01.02.2007 4
2 60 01.03.2007 4
And hence I would like to see the latest report_ID for each partner_id and date, display the Paid value for only the latest report_id.
any more suggestions?
thanks for the quick responses, but unfortunatly none of the suggestions seem to work.
the table will continue like this:
Partner_ID Paid Date Report_ID
1 40 01.01.2007 1
1 40 01.02.2007 1
1 40 01.03.2007 1
1 30 01.01.2007 2
1 30 01.02.2007 2
1 30 01.03.2007 2
2 25 01.01.2007 3
2 25 01.02.2007 3
2 25 01.03.2007 3
2 60 01.01.2007 4
2 60 01.02.2007 4
2 60 01.03.2007 4
And hence I would like to see the latest report_ID for each partner_id and date, display the Paid value for only the latest report_id.
any more suggestions?
SELECT Partner_ID, Paid, DATE, MAX(Report_ID)
FROM tbl_History
WHERE Report_ID = (SELECT MAX(Report_ID) FROM tbl_History)
GROUP BY Partner_ID, Paid, DATE
FROM tbl_History
WHERE Report_ID = (SELECT MAX(Report_ID) FROM tbl_History)
GROUP BY Partner_ID, Paid, DATE
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
SELECT t.*
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) from tbl_History i where i.partner_id = t.partner_id and i.date = t.date )
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) from tbl_History i where i.partner_id = t.partner_id and i.date = t.date )
Ignore the previous post.
SELECT Partner_ID, Paid, DATE, Report_ID
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) FROM tbl_History i
WHERE i.partner_id = t.partner_id
AND i.DATE = t.DATE )
SELECT Partner_ID, Paid, DATE, Report_ID
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) FROM tbl_History i
WHERE i.partner_id = t.partner_id
AND i.DATE = t.DATE )
select PARTNER_ID , PAID , DT , REPORT_ID
from (
select
PARTNER_ID , PAID , DT , REPORT_ID , rank() over(partition by partner_Id, trunc(dt) order by report_id desc) rn
from tbl_history )
where rn = 1
/
from (
select
PARTNER_ID , PAID , DT , REPORT_ID , rank() over(partition by partner_Id, trunc(dt) order by report_id desc) rn
from tbl_history )
where rn = 1
/
ASKER
sorry, these suggestions don't return the correct results...
the table also has a unique key called record_id
the table also has a unique key called record_id
why dont you post a sample set of records and the expected results.
ASKER
set of records:
Record_id Partner_ID Paid DT Report_ID
1 1 40 01.01.2007 1
2 1 40 01.02.2007 1
3 1 40 01.03.2007 1
4 1 30 01.01.2007 2
5 1 30 01.02.2007 2
6 1 30 01.03.2007 2
7 2 25 01.01.2007 3
8 2 25 01.02.2007 3
9 2 25 01.03.2007 3
10 2 60 01.01.2007 4
11 2 60 01.02.2007 4
12 2 60 01.03.2007 4
wished results:
Record_id Partner_ID Paid DT Report_ID
4 1 30 01.01.2007 2
5 1 30 01.02.2007 2
6 1 30 01.03.2007 2
10 2 60 01.01.2007 4
11 2 60 01.02.2007 4
12 2 60 01.03.2007 4
I basically want the latest report_id (max) for each date and partner_id plus the corresponding paid value.
Record_id Partner_ID Paid DT Report_ID
1 1 40 01.01.2007 1
2 1 40 01.02.2007 1
3 1 40 01.03.2007 1
4 1 30 01.01.2007 2
5 1 30 01.02.2007 2
6 1 30 01.03.2007 2
7 2 25 01.01.2007 3
8 2 25 01.02.2007 3
9 2 25 01.03.2007 3
10 2 60 01.01.2007 4
11 2 60 01.02.2007 4
12 2 60 01.03.2007 4
wished results:
Record_id Partner_ID Paid DT Report_ID
4 1 30 01.01.2007 2
5 1 30 01.02.2007 2
6 1 30 01.03.2007 2
10 2 60 01.01.2007 4
11 2 60 01.02.2007 4
12 2 60 01.03.2007 4
I basically want the latest report_id (max) for each date and partner_id plus the corresponding paid value.
Did you try the query posted above?
Including the record_Id as well.
select Record_id, PARTNER_ID , PAID , DT , REPORT_ID
from (
select
Record_id, PARTNER_ID , PAID , DT , REPORT_ID , rank() over(partition by partner_Id, trunc(dt) order by report_id desc) rn
from tbl_history )
where rn = 1
/
Including the record_Id as well.
select Record_id, PARTNER_ID , PAID , DT , REPORT_ID
from (
select
Record_id, PARTNER_ID , PAID , DT , REPORT_ID , rank() over(partition by partner_Id, trunc(dt) order by report_id desc) rn
from tbl_history )
where rn = 1
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sujith80
yes, I tried that SQL, but it didn't return enough records, some were excluded somehow
yes, I tried that SQL, but it didn't return enough records, some were excluded somehow
I have tested with your posted data and it works fine.
The only case where it can eliminate some data is the usage of trunc(dt) in the query, you may try replacing it with dt instead.
The only case where it can eliminate some data is the usage of trunc(dt) in the query, you may try replacing it with dt instead.
please try this:
SELECT t.*
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) from tbl_History i where i.partner_id = t.partner_id )
SELECT t.*
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) from tbl_History i where i.partner_id = t.partner_id )
riffrack:
Did you test what i have posted ?
Did you test what i have posted ?
ASKER
thanks everyone for your help
it looks like nav_kum_v has found exactly what I was looking for, i'm just running some test now.
as the database holds lots of data, it will take me a while to confirm for sure
it looks like nav_kum_v has found exactly what I was looking for, i'm just running some test now.
as the database holds lots of data, it will take me a while to confirm for sure
SELECT Partner_ID, Paid, Date, Report_ID
FROM tbl_History t
WHERE Report_ID = ( SELECT MAX(Report_ID) from tbl_History i where i.partner_id = t.partner_id )
GROUP BY Partner_ID, Paid, Date, Report_ID