mardi 30 octobre 2018

when duplicate values found then

I want to have a query that selects all duplicate values in a column. If those value meet the conditions then I'd like the query to return only those values.

Class    Student_ID  Location
Biology     511         4A
Biology     512         15B
Biology     513         15B
English     514         6A
Biology     521         6A
Spanish     522         6A
Spanish     523         15B
Chemistry   524         4A
English     531         15B
Biology     532         4A
Chemistry   534         4A

Select all duplicate values in the class column and if among those values there is location in both 4A and 15B then assign 1.

CASE WHEN count(class) > 1 AND (Location = '4A' AND Location = '15B') THEN 1 ELSE 0 END

what is most important is how to select duplicate values without specifing for each row. Group by statement doesn't do the trick either.

Aucun commentaire:

Enregistrer un commentaire