SQL Multiple Conditional Minimum Statements

user_18600387 0
Viewed 39

So I have a table with the following columns

Member Version
1 15
2 18
3 316
1 20
3 40
1 316
3 65
2 27
3 65

I want to write an SQL to get the minimum value of "Member", subjected to the following constraints of "Version" (there are around 20 but just using three here):

Where Version between 15 and 20
Where Version in (40, 316)
Where Version between 65 and 66

So the output would look like

Member Version
1 15
1 316
2 18
3 316
3 65

I have this code in a temp table

Select Member, MIN(Version) as Minimum_Version from #Temp
    where Version between 15 and 20
    or Version in (40, 316)
        or Version between 65 and 66
    Group By Member;

I get the fact that my problem is in the Group By, as it is "rolling up" the "Member" column. I am using SQL Server v16 so there is no String_agg.

Thank you for your time and knowledge.

Edit: So I am getting an error Invalid Object Name 'Member'. Here is the updated Code:

With #TempCTE_Model as(
Select Member, Version from #Temp
    where Version Between 15 and 20
        or Version in (40, 316)
        or Version Between 65 and 66)

Select distinct #TempCTE_Model.Member,
        SUBSTRING(
        (
            SELECT ','+cast(#Temp.Version as varchar(100))  AS [text()]
            FROM HIC_NB #Temp
            WHERE #Temp.Member = #TempCTE_Model.Member
            ORDER BY #Temp.Member
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Version]
FROM Member #TempCTE_Model`````
minimum where-clause select sql
1 Answers

first create cte

with a as(
Select a, b from #Temp
    where b between 15 and 20
    or b in (40, 316)
        or b between 65 and 66)

then use For XML PATH as follows

SELECT DISTINCT a2.a, 
    SUBSTRING(
        (
            SELECT ','+cast(a1.b as varchar(100))  AS [text()]
            FROM a a1
            WHERE a1.a = a2.a
            ORDER BY a1.a
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [b]
FROM a a2
user_147999810
Related