Group by question

Database
Junior Supporter

Group by question

I have a situation like this :

I have a code giving correct results:

sel col1,col2,col3,curr_in,rec_in,dw_eff_dt

from tab1

where col1,col2,col3 in

(

sel col1,col2,col3,max(dw_eff_dt)

group by 1,2,3 from tab1

)

I want to take out the record with max(dw_eff_dt) based on the col1,col2 and col3. And then using it in subquey, i want to pull put the record with some extra fields like curr_in,rec_in.

This is giving me a performance problem as the subquery is  an aggregation and again searching the row in the same table. And my tab1 in a big table.

Another solution that is woring fine is :

sel col1,col2,col3,curr_in,rec_in,max(dw_eff_dt)

group by 1,2,3,4,5

But here i get more rows than what is desired and my final o/p rows and changing.

Question is :

Is there a way to do something like this :

sel col1,col2,col3,curr_in,rec_in,max(dw_eff_dt)

group by 1,2,3

I know this would give me an aggregatin error , but i just want to pull out the record with max(dw_eff_dt) by grouping only on col1,col2 and col3 and pull out the curr_in and rec_in for that record.


3 REPLIES
Junior Contributor

Re: Group by question

sel *

from tab

qualify rank (partition by col1,col2,col3 order by dw_eff_dt desc) = 1

GROUP BY -> PARTITION BY

MAX/MIN -> ORDER BY DESC/ASC

Dieter

Junior Supporter

Re: Group by question

Thanks Dieter,

Just one question, performance wise, is using the olap function good ? I have a major concern of performance as my table here is a huge table.

Junior Contributor

Re: Group by question

At least OLAP is performing better than a subquery :-)

To avoid it you have to change the table/ETL design, e.g. maintain a flag indicating the row with the max date.

Dieter