CODEWITHSUNDEEP
mysql
user006779
user006779

Reputation: 1031

how to get number of row with a condition

i have a table like this

id   student teacher
10       a       b
11       c       b
12       d       c
13       e       b

student "e" is 3th student whose teacher is "b". how to get this with one query?

Upvotes: 0

Views: 77

Answers (2)

JScoobyCed
JScoobyCed

Reputation: 10413

Very close to post from Secator

SELECT *
FROM (
    SELECT (@row := @row + 1) AS rank, student
    FROM my_table,
    (SELECT @row := 0) q
    WHERE teacher = 'b'
) s
WHERE student = 'e'

Upvotes: 1

Mariusz Jamro
Mariusz Jamro

Reputation: 31643

You can do it like that:

SELECT * FROM (
   SELECT @row := @row + 1 as row, t.* FROM student_table t, (SELECT @row := 0) r where t.teacher ='b'
) s where student = 'e'

Upvotes: 1

Related Questions