CODEWITHSUNDEEP
sql-server
User 4989588
User 4989588

Reputation: 123

check all the values of a column

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

Upvotes: 4

Views: 58

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460028

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

If there is no odd number all must be even.

Upvotes: 2

mohan111
mohan111

Reputation: 8865

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

Upvotes: 0

ughai
ughai

Reputation: 9880

You can use GROUP BY with HAVING like this.

Query

SELECT [Dept No]
FROM departments
GROUP BY [Dept No]
HAVING SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1
AND SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END) = 0

Explanation

The query returns the departments if there a rollno which is even using SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1. If there is any rollno with odd roll no, SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END) will return non zero sum and that department will be excluded.

Upvotes: 1

Related Questions