How to check all the values in a column is same

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi all , I am new to Sql .Could someone help me in the below problem .
I need to select Batchno whose all status field value is 'Finished' ,if any of the status field value is 'pending' against the Batchno it should not be selected.Any help will be appreciated.

SQL

BatchNo  SID   Status 
2101     123    Finished
2101     457    Pending
2101     478    Finished
2101     8745   Finished
2102     4574   Finished
2102     4578   Finished
2102     4789   Finished
2103     6214   Finished
2103     4236   Pending
2103     7895   Pending

The output should be '2102'

What I have tried:

SELECT Batchno FROM Product where Status='Finished' GROUP BY(Batchno) -This selects all the batchno whose status is pending also.

Posted 5-Jul-17 1:38am

Member 12926744

Updated 10-Jul-17 20:40pm

Thanks7872

Add a Solution

Comments

Michael_Davies 5-Jul-17 8:20am

Please show the results of the SQL, for instance 2101 has Finished and Pending so will show.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeWraith · Accepted Answer · 2017-07-05T02:31:00

Solution 1

Try this:

select distinct BatchNo from TABLE as x
where 
((select count(*) Status from TABLE where BatchNo = x.BatchNo) = 
(select count(*) Status from TABLE where BatchNo= x.BatchNo and Status = x.Status ))

Posted 5-Jul-17 2:31am

CodeWraith

Atlapure Ambrish · Accepted Answer · 2017-07-10T20:40:00

Solution 3

Below is the query..

select distinct BatchNo from groupdata
where BatchNo not in (select BatchNo from groupdata where status = 'Pending')

Posted 10-Jul-17 20:40pm

Atlapure Ambrish

sunithaNag · Accepted Answer · 2017-07-05T09:04:00

Solution 2

SQL

select BatchNo from [UDB_JAX].[dbo].[test] as x
where BatchNo not in
(select BatchNo from [UDB_JAX].[dbo].[test] where BatchNo= x.BatchNo and Status != 'finished' )
group by BatchNo

Posted 5-Jul-17 9:04am

sunithaNag

Updated 5-Jul-17 9:05am

v2