How to select only one row for each unique id, with other columns having certain condition value

Question

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

now that your question is answered, can you please edit it so that it becomes a generic enough question, so that it can potentially help a wider audience in future. E.g. you can take direction from [this question: click here](https://stackoverflow.com/questions/4662464/how-to-select-only-the-first-rows-for-each-unique-value-of-a-column) — Aqeel Ashiq, Aug 23 '22 at 09:23
E.g. At minimum, You can just edit the title to something like: How to select only one row for each unique id, with other columns having certain value — Aqeel Ashiq, Aug 23 '22 at 09:26

Aqeel Ashiq · Accepted Answer · 2022-08-23T09:17:13.863

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.

How to select only one row for each unique id, with other columns having certain condition value

1 Answers1