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
(select * from alldata)
(select var1, var2, max(numeric) as maxnum from step1 group by var1 var2)
(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,