CODEWITHSUNDEEP
sqlsqlite
Vladimir
Vladimir

Reputation: 225

Return values for comparison

The database has the schema students(name TEXT, math_grade INTEGER, physics_grade INTEGER). I want to select the names of all students and a column in which the entry is 1 if there is another student with the same math and physics grades, and it is 0 otherwise. How can I write the query?

Upvotes: 2

Views: 23

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415830

Self join:

select s1.name, case when max(s2.name) is not null then 1 else 0 end
from students s1
left join students s2 on s2.name <> s1.name and s2.math_grade=s1.math_grade and s2.physics_grade=s1.physics_grade
group by s1.name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Hmmm . . . I'm thinking case and exists:

select s.*,
       (case when exists (select 1
                          from students s2
                          where s2.math_grade = s.math_grade and
                                s2.physics_grade = s.physics_grade and
                                s2.name <> s.name
                          )
             then 1 else 0
        end) as is_same_flag
from students s;

Upvotes: 1

Related Questions