Aggregate SQL data - MS SQL server 2008

Question

I have some trouble with grouping the data by the order number and aggregating the "support columns" while selecting always the "highest" value.

╔═════════════╦══════════════╦════════════════╗
║ OrderNumber ║ PhoneSupport ║ ServiceSupport ║
╠═════════════╬══════════════╬════════════════╣
║ 0000000001  ║ 0020         ║                ║
║ 0000000001  ║ 0010         ║ 0030           ║
║ 0000000001  ║ 0010         ║ 0020           ║
║ 0000000002  ║              ║ 0030           ║
║ 0000000002  ║ 0030         ║                ║
║ 0000000003  ║ 0020         ║                ║
║ 0000000003  ║ 0030         ║                ║
╚═════════════╩══════════════╩════════════════╝

In this example the output should be like this.

╔═════════════╦══════════════╦════════════════╗
║ OrderNumber ║ PhoneSupport ║ ServiceSupport ║
╠═════════════╬══════════════╬════════════════╣
║ 0000000001  ║ 0020         ║ 0030           ║
║ 0000000002  ║ 0030         ║ 0030           ║
║ 0000000003  ║ 0030         ║                ║
╚═════════════╩══════════════╩════════════════╝

So far I have often read in various forums something about cursors but I don't like to use it.

My idea was to use the over clause but I am not sure if it is a solution for that case.

Answer 1

Use GROUP BY, do MAX on the columns you want the "highest" value for.

select OrderNumber, max(PhoneSupport), max(ServiceSupport)
from tablename
group by OrderNumber

Answer 2

You can use Group By and Max and then wrap the query in parent to do Order By on aggregate columns. Example shown below.

select * 
from (
    select OrderNumber, max(PhoneSupport) as PhoneSupport, max(ServiceSupport)  as ServiceSupport
    from tablename
    group by OrderNumber) aa
Order By aa.PhoneSupport