CODEWITHSUNDEEP
mysqlsqlcountsubquery
Nitish
Nitish

Reputation: 2763

Count row number from a mysql table subquery

I have a table department_courses with following structure :

id   department_id   name  
--   -------------  -----
1    11             Abcd
2    11             Bghg
3    11             Lopps
4    13             Abvgf

So from this table I need to count the position of the subquery. I mean to say , The position of the name Lopps for department_id is 3 . How to get this in mysql query?

Upvotes: -1

Views: 1364

Answers (3)

redusek
redusek

Reputation: 87

MySQL has a row_number() feature you can utitilize:

SELECT id, 
       department_id, 
       name,
       row_number() over (PARTITION BY department_id ORDER BY department_id, name) as index
FROM department_courses

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

If you only need to do this for one row, then a single query is simpler:

select count(*)
from department_courses dc
where dc.id <= (select dc2.id
                from department_courses dc2
                where dc2.name = 'Lopps'
               );

If you want to assign a row number to all rows, then variables are probably a better method.

Upvotes: 2

Praveen
Praveen

Reputation: 9345

Try:

select row_num
from (
    select t.*, @r := @r + 1 row_num
    from department_courses t,
    (select @r := 0) r
) x
where x.name = 'Lopps'
x.department_id = 3

Upvotes: 1

Related Questions