CODEWITHSUNDEEP
sql
Priyal Bansal
Priyal Bansal

Reputation: 13

Select column value based on other row values

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 :

Gender Marks
M 75
F 88
M 93
M 88
F 98

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:

Gender Marks Same_Marks
M 75 0
M 93 0
M 88 1

Upvotes: 1

Views: 70

Answers (2)

Jonas Metzler
Jonas Metzler

Reputation: 5975

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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;

Upvotes: 1

Related Questions