Cleaner sql statement to get records at max value

Professional Actuarial Topics Property/Casualty
1

I use t-sql, the microsoft one, and i’m pretty sure there’s a better way to accomplish what i’m trying to do in fewer CTE statements, i just don’t know how.

I have a large data set with pol, var1, var2, numeric. I want the max(numeric) by var1 and var2, then to find the policy that has the max numeric, so i’d normally have something like

step1 as
(select * from alldata)
,
step2 as
(select var1, var2, max(numeric) as maxnum from step1 group by var1 var2)
,
step3 as
(select s1.* from step1 s1 inner join step2 s2 on s1.var1=s2.var1 and s1.var2=s2.var2 and s1.numeric=s2.maxnum)
select * from step3

Then i get all of the pols at the max field. Isn’t there a more efficient way to do this using the having function?

This is a simplified example of my real world situation.

Till all are one,

Epistemus

2 
select pol, var1, var2, numeric
from (
      select pol, var1, var2, numeric, max(numeric) over(partition by var1, var2) max_num
      from alldata
      )dt
where  numeric = max_num
1 Like
3

That did it @knoath! Thanks!

I even posted on the wrong forum on accident. Oops!

4

No worries. I did use stackoverflow to check the syntax so I recommend using that as a resource.