All Forums Database
Sai088 13 posts Joined 03/12
30 Nov 2015
How to populate max value rows with in the group by eliminating the min values

I have a table like below
 
Id startdate enddate rate
1 1/1/2015 2/1/2015 10
2 2/1/2015 3/1/2015 15
3 3/1/2015 4/1/2015 5
4 4/1/2015 5/1/2015 10
5 5/1/2015 6/1/2015 20
6 6/1/2015 7/1/2015 30
7 7/1/2015 12/31/2015 10

 

 

I need to populate only max value records by eliminating min values  like below

 

Id startdate enddate rate

1 1/1/2015 2/1/2015 10

2 2/1/2015 3/1/2015 15

5 5/1/2015 6/1/2015 20

6 6/1/2015 7/1/2015 30

 

 

Can any one help me on this

 

 

ulrich 816 posts Joined 09/09
30 Nov 2015

try the following - pseudo code not tested against the DB
 

select id,
       startdate, 
       enddate,
       rate
from yourTable
qualify rate > max(rate) over (order by start_date rows between unbounded preceding and 1 preceding)
or  max(rate) over (order by start_date rows between unbounded preceding and 1 preceding) is null
order by id

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Sai088 13 posts Joined 03/12
30 Nov 2015

Hi ulrich,
 
Thank you very much for the quick response. I sthere any way to wirte sql without using analytical (OLAP)  functions?
 
Regards,
Sai.

ulrich 816 posts Joined 09/09
30 Nov 2015

maybe but more complicated - you need some kind of product join - and won't get the same performance.
Why don't you want to use the OLAP function?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Sai088 13 posts Joined 03/12
30 Nov 2015

I need to run the above logic on SQL server as well. I donot think SQL server will support the above logic.
 
Thanks,
Sai.

dnoeth 4628 posts Joined 11/04
30 Nov 2015

Hi Sai,
SQL Server support Cumulative Sums since SS2012, only QUALIFY is Teradata proprietary syntax, which can easily be replaced:

select *
from
  ( select id,
       startdate, 
       enddate,
       rate,
       max(rate) 
       over (order by start_date 
             rows between unbounded preceding and 1 preceding) as max_rate
    from yourTable
  ) as dt
where rate > max_rate 
   or max_rate is null
order by id

 

Dieter

Sai088 13 posts Joined 03/12
30 Nov 2015

Thanks dnoeth....currently we are using ss2008 that is why i am expecting the logic without OLAP function. The above logic working as expected on teradata but we need to use the same logic on sql server as well. 
 
 
Thanks,
Sai.

You must sign in to leave a comment.