question

santhu avatar image
santhu 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
sql-server-2008sql-server-2005sql-server-2008-r2tsql
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

virtualjosh avatar image
virtualjosh answered
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

untitled.jpg (30.7 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.