filter records with conditions-CodePudding

I have a table with two columns one the 'clientID' and the second 'usable'. A clientID can appear several times in my table.

If only one occurrence of this clientID exists with a 'NO' it means that in my final table it will have to be tagged 'NO'.

If it has only occurrences with 'YES' it means that it will be tagged in my final table with 'YES'.

how i can make this final table ?

CREATE TABLE InitialValues (
    idClient varchar(5),
    usable varchar(3),
);


insert into InitialValues(idClient,usable) values
('c1234','yes'),
('c1334','yes'),
('c1334','no'),
('c1434','yes');

select * from InitialValues


CREATE TABLE FinalValues (
    idClient varchar(5),
    usable varchar(3),
);


insert into FinalValues(idClient,usable) values
('c1234','yes'),
('c1334','no'),
('c1434','yes');

select * from finalValues

Example

CodePudding user response：

You can do it without creating a second FinalValues table. You can select from InitialValues in such a way that you don't have to create a redundant table.

SELECT idClient, usable FROM
(
SELECT *
, ROW_NUMBER() OVER(PARTITION BY idClient ORDER BY usable) AS row
FROM InitialValues
) as a
WHERE row = 1

What it does is that it sorts the rows by usable column. So that rows with no will come on the top. And then it selects first row only for each id. Ofcourse, each id's first row will have usable column as no if there was one for that id. If that id did not have any no only then there will be yes

However, if you still want to create a second table for some other requirement. You can still do so, and Insert the resultset of above select query into second table.