# Cleaner sql statement to get records at max value

**URL:** <https://community.goactuary.com/t/cleaner-sql-statement-to-get-records-at-max-value/3866>\
**Category:** Property/Casualty\
**Created:** [August 30, 2021, 3:45pm UTC](https://community.goactuary.com/t/cleaner-sql-statement-to-get-records-at-max-value/3866 "2021-08-30T15:45:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![epistemus](https://community.goactuary.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@epistemus](https://community.goactuary.com/u/epistemus)\
**Post date:** [August 30, 2021, 3:45pm UTC](https://community.goactuary.com/t/cleaner-sql-statement-to-get-records-at-max-value/3866/1 "2021-08-30T15:45:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knoath](https://community.goactuary.com/user_avatar/community.goactuary.com/knoath/32/65607_2.png) [@knoath](https://community.goactuary.com/u/knoath)\
**Post date:** [August 30, 2021, 4:28pm UTC](https://community.goactuary.com/t/cleaner-sql-statement-to-get-records-at-max-value/3866/2 "2021-08-30T16:28:42Z")

</div>

```auto
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
```

---

<div class="post-metadata">

**Author:** ![epistemus](https://community.goactuary.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@epistemus](https://community.goactuary.com/u/epistemus)\
**Post date:** [August 30, 2021, 5:07pm UTC](https://community.goactuary.com/t/cleaner-sql-statement-to-get-records-at-max-value/3866/3 "2021-08-30T17:07:58Z")

</div>

That did it @knoath! Thanks!

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

---

<div class="post-metadata">

**Author:** ![knoath](https://community.goactuary.com/user_avatar/community.goactuary.com/knoath/32/65607_2.png) [@knoath](https://community.goactuary.com/u/knoath)\
**Post date:** [August 30, 2021, 5:20pm UTC](https://community.goactuary.com/t/cleaner-sql-statement-to-get-records-at-max-value/3866/4 "2021-08-30T17:20:18Z")

</div>

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