I tried for hours and read many posts but I still can't figure out how to handle this request:

I have a table like this :

I'd like to select all boys from the table and set the sameMarks column to 1 when the boy marks match the girl marks, otherwise it should be 0.
The output should look like this:

CodePudding user response：

One possible approach would be aggregation:

SELECT MAX(Gender) AS Gender,
       Marks,
       CASE WHEN MIN(Gender) = MAX(Gender) THEN 0 ELSE 1 END AS Same_Marks
FROM yourTable
GROUP BY Marks;

CodePudding user response：

One option is using EXISTS to check if women with the same mark exist and applying a CASE WHEN on the result:

SELECT 
y.gender, y.marks,
CASE WHEN 
  EXISTS(SELECT 1 FROM yourtable WHERE gender <> 'M' and marks = y.marks) 
  THEN 1 ELSE 0 END AS Same_Marks
FROM yourtable y
WHERE y.gender = 'M';

Note: This answer assumes you really want to get boys only, no women (according to your description). If this is incorrect, please review and improve your question.

Like Tim already mentioned, it would be much better to use 'B' for both genders.