[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4086
  • Last Modified:

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?

0
riffrack
Asked:
riffrack
  • 5
  • 5
  • 3
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:

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
0
 
sujith80Commented:
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
/
0
 
MikkkCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
riffrackAuthor Commented:
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?
0
 
darshan6Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 )
0
 
darshan6Commented:
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 )
0
 
sujith80Commented:
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
/
0
 
riffrackAuthor Commented:
sorry, these suggestions don't return the correct results...

the table also has a unique key called record_id
0
 
sujith80Commented:
why dont you post a sample set of records and the expected results.
0
 
riffrackAuthor Commented:
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.
0
 
sujith80Commented:
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
/
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you try this one and see if it works for you :

select x.partner_id partner_id, x.paid paid, x.date date, x.mm report_id
from
(SELECT Partner_ID, Paid, Date, MAX(Report_ID) mm
FROM tbl_History
GROUP BY Partner_ID, Paid, Date ) x,
(SELECT Partner_ID, Date, MAX(Report_ID) nn
FROM tbl_History
GROUP BY Partner_ID, Date ) y
where x.partner_id = y.partner_id
and x.date = y.date
and x.mm = y.nn ;

Thanks
0
 
riffrackAuthor Commented:
sujith80
yes, I tried that SQL, but it didn't return enough records, some were excluded somehow
0
 
sujith80Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 )

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
riffrack:

 Did you test what i have posted ?
0
 
riffrackAuthor Commented:
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

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now