T-SQL order by and take only the highest value

Question

I have a table where i want to retrieve the highest values of a category ea:

UserA catA   4
userA catA 200
userA catB 100
UserA catA  50
userB catA 150
userB catC  10
userB catC 300

it should return

userA catA 200
userA catB 100
userB catA 150
userB catC 300

Order by and then take only the highest value. And i wonder what's the best way to do this.

Possible duplicate of [How does GROUP BY work?](https://stackoverflow.com/questions/7434657/how-does-group-by-work) — Magnus, Mar 26 '19 at 08:42
group by can be used in many ways and sure stack overflow has grown so vast that somewhere some place in some answers something alike it is available however its a short question and there is a short answer here as well. — Peter, Mar 27 '19 at 07:41

score 1 · Accepted Answer · answered Mar 26 '19 at 07:28

1

Select column1, column2, max(column3) as MaxColumn3 from TableName group by column1, column2

as simple as that.

answered Mar 26 '19 at 07:28

Whencesoever

score 0 · Answer 2 · answered Mar 26 '19 at 07:37

Another option can be using MAX() function with Partition By clause as follows

Select 
    distinct [user], category, max([value]) over (partition by [user], [category]) as MaxValue 
from UserCategoryValues

Many aggregation functions can be used with Partition By clause in SQL

score 0 · Answer 3 · answered Mar 26 '19 at 07:51

0

Select   [User], Category,max(Value) from UserCategory group by [User], Category order by [User]

answered Mar 26 '19 at 07:51

Bilal Ahmed

Please add some explanation to your answer such that others can learn from it – Nico Haase Mar 26 '19 at 08:25

3 Answers3