Question: select all the departments having students with even roll number

```
Dept No     Roll No   Student Name 

 1         1          lee 
 1         2          scott
 2         2          scott
 2         4          smith
 1         4          smith

```

This should result in DEpt no 2 as it has only students with roll number divisible by 2

Answer: 
Another(imo easy and lightweight) way is using `NOT EXISTS` and `DISTINCT`:

```
SELECT DISTINCT [Dept No]
FROM dbo.TableName t
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.TableName t2
    WHERE t.[Dept No] = t2.[Dept No]
      AND t2.[Roll No] % 2 = 1
)

```

`[**Demo**](http://sqlfiddle.com/#!6/f4ba1/1/0)`

If there is no odd number all must be even.
```
declare @t table (Dept int,Rno int,Student varchar(10))
insert into @t (Dept,Rno,Student)values (1,1,'lee'),(1,2,'scott'),(2,2,'scott'),(2,4,'smith'),(1,4,'smith')

SELECT Dept,Rno,Student 
FROM (SELECT ROW_NUMBER () OVER (ORDER BY Rno DESC) row_number, Dept,Rno,Student  
    FROM @t) a WHERE (row_number%2) = 0

```