CODEWITHSUNDEEP
mysqlsqldatabase
Jimit
Jimit

Reputation: 2259

How to get all students whose mark is greater than particular student

I have table called students and it is looks like below.

id name mark 
1   A    10
2   B    20
3   C    30
4   D    40

Now, How can I get all students whose marks is greater than student name 'B'. I can do with below query.

SELECT * from student WHERE mark > (SELECT mark FROM student WHERE name = 'B');

Is it possible without sub query?

Upvotes: 0

Views: 1144

Answers (2)

Rahul
Rahul

Reputation: 77876

Yes it is with a INNER JOIN by double joining the table like below

SELECT s.* 
from student s
join student s1
on s.mark > s1.mark
and s1.name = 'B';

(OR) Using WHERE EXISTS like

SELECT * from student s
WHERE EXISTS (SELECT 1 
                FROM student 
                WHERE name = 'B'
                AND s.mark > mark);

Upvotes: 1

bbrumm
bbrumm

Reputation: 1352

You could try using a stored procedure to store the mark of student B into a variable, then use that variable in the WHERE clause of the first query.

However, I think subqueries would be the best way.

Why would you want to avoid using a subquery?

Upvotes: 0

Related Questions