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.

Bridge · Accepted Answer · 2015-11-26 09:13:59Z

4

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

edited Nov 26, 2015 at 9:13

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

answered Nov 26, 2015 at 9:07

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian S. Over a year ago

Thanks for opening my eyes. I simplified the problem in my question, with your short but correct answer I recognized that the problem is in the cte before...

Avinash Jain · Accepted Answer · 2015-11-26 09:11:55Z

1

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

answered Nov 26, 2015 at 9:11

Avinash Jain

7,6862 gold badges29 silver badges40 bronze badges

2 Comments

Evaldas Buinauskas Over a year ago

That derived table is unneeded.

jarlh Over a year ago

Can't you just put the ORDER BY at the end, without using a derived table? Or is that some kind of MS SQL Server limitation?

Collectives™ on Stack Overflow

Aggregate SQL data - MS SQL server 2008

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related