CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
User247365
User247365

Reputation: 685

Binary filter for max value of SQL query

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

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

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

Matt
Matt

Reputation: 14381

SELECT [ID]
      ,MAX([Value1]) as Value1
      ,MAX([Value2]) as Value2
FROM [DataTable]
GROUP BY
    [ID]

Upvotes: 1

Related Questions