How can I get all the rows of the MAX value in SQL?-CodePudding

I would like to know how I can get all the rows with the maximum order value based on Cat 1,Cat 2 and Month here. I tried using Partition and MAX but he gives me the same max value even if the month is different.

enter image description here

What I tried:

Select
ID
,Cat1
,Cat2
,Month
,max([Order]) Over
      (Partition By [Cat1],[Cat2],[Month]) as max_ord
from table
Where [order] = max_ord

What I need:

tab result

CodePudding user response：

Wrap your query within a subquery as the following:

Select ID ,Cat1 ,Cat2 ,Month ,[Order]
From
(
  Select ID, Cat1 ,Cat2 ,Month ,[Order],
  MAX([Order]) Over (Partition By [Cat1],[Cat2],[Month]) As max_ord
  From table_name
) T
Where [order] = max_ord
Order By ID

Or, you could give it a try with CTE:

With CTE As
(
  Select ID, Cat1 ,Cat2 ,Month ,[Order],
  MAX([Order]) Over (Partition By [Cat1],[Cat2],[Month]) As max_ord
  From table_name
)
Select ID ,Cat1 ,Cat2 ,Month ,[Order]
From CTE
Where [order] = max_ord
Order By ID

See demo.