MySQL - Get row number post select & join

Question

I am trying to obtain the row number (i.e. rank) for the following select statement (which includes a column JOIN) but without declaring a SET variable at the beginning.

Reason for this is because I am using a WordPress/MySQL plugin which can only emulate single statement code. The common hack of declaring a prior variable to 0 then incrementing is not recognized.

Is there another way to obtain the row number using the select & join below?

SELECT s.id
     , s.item
     , s.state
     , c.job_count 
  FROM wp_state_tab s
  LEFT 
  JOIN wp_custom_tab c
    ON c.state_id = s.id 
 WHERE c.date = CURDATE()  
 ORDER 
    BY c.job_count DESC

Sample Data Output

MySQL version is 5.6.40-84.0-log

Possible duplicate of [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Madhur Bhaiya, Jul 17 '19 at 13:58

score 0 · Answer 1 · answered Jul 17 '19 at 13:59

0

You can use a subquery for the iteration of a newly defined row number without explicitly declaring variable as :

   select @i :=  @i + 1 as rownum, 
          s.id, s.item, s.state, c.job_count 
     from wp_state_tab s
     left join wp_custom_tab c
       on c.state_id = s.id and c.date = CURDATE()
     join (select @i:=0) t2 
    order by job_count desc;

answered Jul 17 '19 at 13:59

Barbaros Özhan

59,113
10
31
55

This code is returning the true row number (same result as 'id' column). Not the row number (i.e. rank) as per the sort. – d3lt4 Jul 17 '19 at 14:08
Correct - The rank or essentially the row number based on the select output – d3lt4 Jul 17 '19 at 14:16

score 0 · Accepted Answer · answered Jul 17 '19 at 14:22

MySQL can be fiddly about variables -- good thing they are deprecated. With ORDER BY or GROUP BY, you often have to use a subquery:

SELECT (@rn := @rn + 1) as seqnum, sc.*
FROM (SELECT s.id, s.item, s.state, c.job_count 
      FROM wp_state_tab s LEFT JOIN
           wp_custom_tab c
           ON c.state_id = s.id
      WHERE c.date = CURDATE()  
      ORDER BY c.job_count DESC
     ) sc CROSS JOIN
     (SELECT @rn := 0) params;

MySQL - Get row number post select & join

2 Answers2