How I can filter out a value in my SQL query based on a condition-CodePudding

Can someone please help me with the below issue that is driving me to madness lately:

I have a table with article numbers, the component used for that article and a change number.

One article should have one component but because of production issues sometimes it happens that it has an old and a new component. In this case a change number is present.

My real question is: How can I build a select query which filters all the articles components where the change number is NULL, and only the new component for the articles where the change number is present.

Article:    Component:  Change Number:
00011873    01044541    NULL             -Not required in list
00011873    01040994    000000000001     -Should be present in list

00011875    01020151    NULL             -Should be present in list
00011881    01025465    NULL             -Should be present in list
00011883    01045066    NULL             -Should be present in list

I am only at beginner level with SQL databases and any help would be greatly appreciated.

Thank you very much!

CodePudding user response：

If you want all rows for an article where the change is not null and then null for the remaining articles, you can put this logic in the where clauses using not exists:

select t.*
from t
where t.change_number is not null or
      not exists (select 1
                  from t t2
                  where t2.article = t.article and
                        t2.change_number is not null
                 );

This is a SQL translation of the first part of this answer.