Reputation: 685
I am using Microsoft SQL Server Management Studio. I am very new to SQL and really do not even know how to start going about solving this so I apologize in advance for the lack of already attempted solutions. A simplified example of my current SQL Query is as follows:
USE [DataBase]
GO
SELECT [ID]
,[Value1]
,[Max1]
,[Value2]
,[Max2]
FROM [DataTable]
Which yields something like so:
[ID] [Value1] [Max1] [Value2] [Max2]
[1] [2] [0] [9] [1]
[1] [4] [0] [3] [0]
[1] [8] [1] [5] [0]
[1] [5] [0] [6] [0]
[2] [1] [0] [1] [0]
[2] [6] [1] [4] [0]
[2] [3] [0] [3] [0]
[2] [2] [0] [5] [1]
I am trying to get my datatable (DataTable) to select the value corresponding to the binary indicator (Max1 and Max2 columns for Value1 and Value2 respectively) that it is the maximum value for that ID. Basically I am trying to get the above table to become:
[ID] [Value1] [Value2]
[1] [8] [9]
[2] [6] [5]
If it is much simpler to create a separate data frame for each variable (Value1 is in a separate resultant data frame as Value2), that is fine as well.
Thank you
Upvotes: 0
Views: 364
Reputation: 49270
You can use conditional aggregation to pick up the values corresponding to the binary values 1.
select
[ID]
,max(case when [Max1] = 1 then [Value1] end) as value1
,max(case when [Max2] = 1 then [Value2] end) as value2
from [DataTable]
group by [ID]
Upvotes: 1
Reputation: 14381
SELECT [ID]
,MAX([Value1]) as Value1
,MAX([Value2]) as Value2
FROM [DataTable]
GROUP BY
[ID]
Upvotes: 1