CODEWITHSUNDEEP
mysqlsql
emersonthis
emersonthis

Reputation: 33378

MySQL: how to number rows with a LEFT JOIN

My MySQL query is for a report, so I want to number each row. In other words, I want to add a field like "rank" and the first row will be 1, the second 2, etc.

I've seen this done using a subquery here and here. Judging from the comments, this method seems correct, but it doesn't work for my specific query. I believe it is because I have several JOINs, but I'm not sure. I can get the query to run without error, but when I add the row numbers exactly as instructed in the above posts, the "rank" column is out of order.

Here is my original query, without the ranks added:

SELECT wsat_ib.user_id, wpjb_resume.id resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, GROUP_CONCAT(DISTINCT wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, GROUP_CONCAT(wpjb_application.job_id) AS applications FROM  `wsat_ib` 
    LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
    LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
    LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id AND wpjb_field_value.field_id=3
    LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id 
    WHERE (wp_usermeta.meta_key = 'target_employer' AND (wp_usermeta.meta_value = 'public' OR wp_usermeta.meta_value=2) AND wpjb_resume.is_active =1)
    GROUP BY wsat_ib.user_id, resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, wpjb_field_value.value
     ORDER BY end_time DESC LIMIT 0, 20

Here is my unsuccessful attempt to add the rank field:

SELECT @row := @row + 1 as row, wsat_ib.user_id, wpjb_resume.id resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, GROUP_CONCAT(DISTINCT wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, GROUP_CONCAT(wpjb_application.job_id) AS applications FROM (SELECT @row := 0) r, `wsat_ib` 
LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id AND wpjb_field_value.field_id=3
LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id 
WHERE (wp_usermeta.meta_key = 'target_employer' AND (wp_usermeta.meta_value = 'public' OR wp_usermeta.meta_value=2) AND wpjb_resume.is_active =1)
GROUP BY wsat_ib.user_id, resume_id, wpjb_resume.firstnam[...]

The query runs without error, but the rank column is not 1, 2, 3, 4... Instead it's: 496,498,497,499...

What am I doing wrong?

Upvotes: 0

Views: 2862

Answers (1)

Don Cruickshank
Don Cruickshank

Reputation: 5938

Your row numbers are not as you expect because your SQL query has a GROUP BY clause in it. The SELECT line is evaluated on each row before it is grouped.

To solve your problem you can use a nested query, e.g.:

SELECT *, @row := @row + 1 AS row FROM (SELECT ...) AS t;

Your query that you provided would then be:

SELECT *, @row := @row + 1 AS row FROM (
    SELECT wsat_ib.user_id, wpjb_resume.id resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, GROUP_CONCAT(DISTINCT wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, GROUP_CONCAT(wpjb_application.job_id) AS applications FROM  `wsat_ib`
        LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
        LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
        LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id AND wpjb_field_value.field_id=3
        LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id
        WHERE (wp_usermeta.meta_key = 'target_employer' AND (wp_usermeta.meta_value = 'public' OR wp_usermeta.meta_value=2) AND wpjb_resume.is_active =1)
        GROUP BY wsat_ib.user_id, resume_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, wpjb_field_value.value
        ORDER BY end_time DESC LIMIT 0, 20) AS t
  CROSS JOIN
    (SELECT @row := 0) AS r 
ORDER BY end_time DESC ;

Upvotes: 2

Related Questions