• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4103
  • 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
 
SujithData ArchitectCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
SujithData ArchitectCommented:
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
 
SujithData ArchitectCommented:
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
 
SujithData ArchitectCommented:
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
 
SujithData ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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