I'm certain that this is a simple WHERE clause. I'm just not sure how to write it.
Let's say I have the following data where a,b,c(numbers) and d(varchar):
What I'd like to do is select rows that have the following conditions:
This should do it:
IF OBJECT_ID('tempdb..#TABLE') IS NOT NULL DROP TABLE #TABLE CREATE TABLE #TABLE (A int, B int , C int) INSERT INTO #TABLE ( A, B , C ) VALUES (0,1,0), (1,1,253), (2,4,800), (3,5,460), (4,6,300), (5,7,350), (6,7,450)
;WITH CTE --<-- this is a common table expression, it is essentially the same as having temp tables but it is in memory. Just google CTE tSQL and you will find out more about it. AS (SELECT * , rn = ROW_NUMBER() OVER(PARTITION BY B ORDER BY A) --this assigns a row number to the rows that are partitioned by the values in B. FROM #TABLE) SELECT A , B , C FROM CTE WHERE B IN (SELECT B --<-- here filter to only return the rows that have an rn (row number) greater than 1 meaning there are more than one row for the given value in B FROM CTE WHERE rn > 1) AND B IN (SELECT B --<-- here we filter further to ensure at least 1 record has the value 0. FROM CTE WHERE C = 0 AND B IN (SELECT B --<--this is to ensure that at least there is one value that is not 0 to handle the bug pointed out by Jay. FROM CTE WHERE C <> 0));
More about CTE's
More about ROW_NUMBER()