I need to filter only that rows which is having only 'N' against the id

Question

1.29/5 (5 votes)

See more:

id Generated
=========================
1 N
1 N
1 Y
2 Y
2 Y
3 N
3 N
3 N
4 N
5 Y

from the above table i need only that id which is having 'N' for all its rows.

i tried by
select distinct id from table where id not in (select distinct id from table where generated='Y')

table is having so many records, when i run my query its gives error that temp is out of memory(dnt remember exact words)

Could you please provide me some other way to to achieve the output?

Posted 16-Oct-14 18:14pm

princedesai

Add a Solution

Comments

Schatak 17-Oct-14 1:26am

You want distinct record for this?

8 solutions

Solution 4

SQL

SELECT DISTINCT
    id
FROM
    your_table
WHERE
    col = 'N'
EXCEPT
SELECT
DISTINCT
    id
FROM
    your_table
WHERE
    col = 'y'

Posted 16-Oct-14 20:20pm

Arora_Ankit

Solution 6

Try this

SQL

select * from #temp t1 where Generated='N'
and not exists
(select ID from #temp t2 where Generated='Y' and t1.Id=T2.Id)

and do apply distinct on selection afterwords so that your query processing does not take much time.

Posted 16-Oct-14 23:43pm

Shweta N Mishra

Solution 5

select * from yourtablename where Generatedlike('%N%')
or
select * from yourtablename where Generated='N'
or
select Distinct id from yourtablename where Generated='N'

you can try any one query from above ,You may get required selection ,
I Think it will be solve you are problem

Regards

K Ramesh

Posted 16-Oct-14 22:08pm

rameshkashanna

Comments

CHill60 18-Oct-14 7:43am

This would include id=1 which also has a Y row

Solution 7

Use the Query as :

select distinct id from (select id from tablename where code <> 'Y');

Posted 17-Oct-14 22:53pm

MukeshSagar

Solution 8

this is the simplest solution . . . .
select Distinct id from yourtablename where Generated='N'

Posted 17-Oct-14 23:27pm

Santhosh Babu Mahimairaj

Comments

CHill60 18-Oct-14 7:42am

This would include id=1 which also has a Y row

Solution 1

select distinct id from table where Generated='N';

Posted 16-Oct-14 18:33pm

vJay Yadav

Comments

Sanchayeeta 17-Oct-14 1:21am

This will result all IDs whichever having at least one Generated='N'

princedesai 17-Oct-14 1:41am

I need only that values where for that specific id there is no Y in any column.

for eg. 1 is having one Y in 3rd row.. so it should be neglected.

according to the above table only 3 and 4 should be returned..

Sanchayeeta 17-Oct-14 1:45am

yes, and this will not return id which is having 'N' for all its rows.

princedesai 17-Oct-14 1:48am

Correct sanchayeeta..

Solution 3

select distinct id from table where Generated like 'N';

Posted 16-Oct-14 20:09pm

jagadeeshkumar.tangudu

Comments

CHill60 18-Oct-14 7:42am

This would include id=1 which also has a Y row

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)

Magic Wonder · Accepted Answer · 2014-10-16T19:32:00

Solution 2

Hi,

Check this...

SQL

select * from your_table where id not in (select id from your_table where generated='Y' )

Hope this will help you.

Cheers

Posted 16-Oct-14 19:32pm

Magic Wonder

Comments

Sanchayeeta 17-Oct-14 1:41am

My vote 5

Magic Wonder 17-Oct-14 1:42am

thnx.

princedesai 17-Oct-14 1:43am

I have written the same query above in my question... this is not working bcz there are lot many data

Magic Wonder 17-Oct-14 2:07am

How many records are there in your table?