asked
Filter the data with GROUP BY in SQL Server
I have table with data something like this > Id value1 value2 IsIncluded > ---------------------------------- > 1859 1702 4043 0 > 1858 1706 4045 0 > 1858 1703 4046 1 > 1860 1701 4046 0 > 1861 1702 4047 0 To get the Ids with min(value1) and max(value2) and filter based on included column I can do something like this select Id, min(value1), max(value2) from table where IsIncluded = 0 group by Id and I get the result >Id value1 value2 IsIncluded >----------------------------------- >1859 1702 4043 0 >1858 1706 4045 0 >1860 1701 4046 0 >1861 1702 4047 0 but can I filter the data more if there is 1 in IsIncluded for that Id then it shouldn't pick up that row. say this is my table ` >id isinc >1 0 >1 1 >2 0` i only wat to display id 2 0 bcoz id 1 has a row with isinc =1 , I want to completely take it of my list even if it has another row with isinc =0 I only want ID with isincluded value 0 but not ID with isincluded value 0 and 1 I should only get Ids with isIncluded value 0. But if there is another entry for an Id with IsIncluded 1 then all entries with that Id must be avoided
This web site runs off of voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer leads to a solution, please indicate that by clicking on the check mark next to that answer.
How about a LEFT JOIN to a derived table. This is pseudo code, so you'll need to adjust the real T-SQL on your end: SELECT * FROM TableA AS a LEFT JOIN (SELECT ID FROM TableA WHERE ID = 1) AS b ON
a.ID =
b.ID WHERE
b.ID IS NULL AND
a.ID = 0 That ought to eliminate the rows that have 1 or 1 and 0 and only include the values that have 0.
The LEFT JOIN above will cause duplicate rows if one id has more than one row with IsIncluded = 1. I would try something like this: -- Create some test data SELECT 1 AS id , 1 AS value , 0 As isincluded INTO #Table UNION ALL SELECT 1, 2, 0 UNION ALL SELECT 1, 3, 1 UNION ALL SELECT 2, 4, 0 UNION ALL SELECT 2, 5, 0 UNION ALL SELECT 2, 6, 0 UNION ALL SELECT 3, 7, 1 UNION ALL SELECT 3, 8, 1 UNION ALL SELECT 3, 9, 1 ; -- Review data SELECT * FROM #Table; -- Get data Summary WITH excluded AS ( SELECT id FROM #Table WHERE IsIncluded = 1 ) SELECT id , min(value) AS min , max(value) AS max FROM #Table WHERE IsIncluded = 0 AND id NOT IN (SELECT id FROM excluded) GROUP BY id ORDER BY id ; -- Maintennance DROP TABLE #Table; ![alt text][1] [1]: /storage/temp/1654-untitled.jpg
16 People are following this question.
