How to populate max value rows with in the group by eliminating the min values

Database
Enthusiast

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

Tags (1)
6 REPLIES
Senior Supporter

Re: How to populate max value rows with in the group by eliminating the min values

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
Enthusiast

Re: How to populate max value rows with in the group by eliminating the min values

Hi ulrich,

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

Regards,

Sai.

Senior Supporter

Re: How to populate max value rows with in the group by eliminating the min values

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?

Enthusiast

Re: How to populate max value rows with in the group by eliminating the min values

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

Thanks,

Sai.

Junior Contributor

Re: How to populate max value rows with in the group by eliminating the min values

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
Enthusiast

Re: How to populate max value rows with in the group by eliminating the min values

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.