CODEWITHSUNDEEP
sql
KEVINBOSS
KEVINBOSS

Reputation: 149

SQL select statement where one course more than other

enter image description here

what I want is return Name A because A is the only one who scored English better than Geo.

The basic question is select all the student where english score > geo score

Upvotes: 1

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can do this using group by and having:

select name
from t
where course in ('Eng', 'Geo')
group by name
having max(case when course = 'Eng' then score end) > max(case when course = 'Geo' then score end);

If you have only one score per name/course, you can also use joins such as:

select teng.name
from t teng join
     t tgeo
     on teng.name = tgeo.name and
        teng.course = 'Eng' and
        tgeo.course = 'Geo' and
        teng.score > tgeo.score;

Upvotes: 3

Related Questions