Problem with MySQL Join

Question

I need some help on a MySQL SELECT statement. Unfortunately, this statement doesn't work correctly. It appears to rank based on alphabetical order of the school name instead of reading_percent_proficient_and_advanced. How should I fix it?

SET @rank=0;
SELECT @rank:=@rank+1 as rank, 
       sy.formatted_school_name, 
       FLOOR(d.reading_percent_proficient + d.reading_percent_advanced) AS reading_percent_proficient_and_advanced 
FROM d_test_scores AS d, 
       sy_2010_2011_school_type AS sy  
WHERE sy.school_id = d.school_id 
       AND sy.school_group = 'public school'
       AND sy.school_type ='elementary' 
ORDER BY reading_percent_proficient_and_advanced DESC

If I take out the join, then this statement gives me the correct ranking (but I don't have the school names then):

SET @rank=0;
SELECT @rank:=@rank+1 as rank,  
       d.school_id, 
       FLOOR(d.reading_percent_proficient + d.reading_percent_advanced) AS reading_percent_proficient_and_advanced  
FROM d_test_scores AS d 
ORDER BY reading_percent_proficient_and_advanced DESC;

What's the problem with my join?

The query as written should work. Could you give some sample data and results of each query? — Michael Mior
– Michael Mior, Commented Aug 17, 2011 at 19:27

Sam DeHaan · Accepted Answer · 2011-08-17 19:35:21Z

1

(Speculation) I believe the rank is being calculated before the ORDER BY ordering is enforced. While in the original query, it just happened that your results came back in the order you wanted before the ORDER BY and thereby the rank was correct, in the advanced query the results are initially coming back in a different order.

Try adding the rank to an already ordered set (subquery) of school name and proficiency ratings.

answered Aug 17, 2011 at 19:35

Sam DeHaan

10.4k2 gold badges42 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laxmidi Over a year ago

I think that you're right, Sam. I tried this query: "SET \@rank=0; SELECT \@rank := \@rank +1 AS rank, reading_percent_proficient_and_advanced, school_id FROM (SELECT d.school_id, FLOOR( d.reading_percent_proficient + d.reading_percent_advanced ) AS reading_percent_proficient_and_advanced FROM dc_nclb_scores_2011 d ORDER BY reading_percent_proficient_and_advanced DESC) AS my_table" and it gave me the correct rank. I need to figure out how to get the formatted_school_name from the sy_2010_2011_school_type sy table. It should join on sy.school_id = d.school_id. Almost there. Thank you, Sam.

Laxmidi Over a year ago

re: \@rank. I had to add the slash or else it wouldn't let me post the comment.

Laxmidi Over a year ago

Thank you Sam DeHaan and Phoenix!! I combined your ideas. This works: "SET @rank=0; SELECT @rank:=@rank +1 as rank, formatted_school_name, school_id, reading_percent_proficient_and_advanced FROM (SELECT sy.formatted_school_name, FLOOR(d.reading_percent_proficient + d.reading_percent_advanced) AS reading_percent_proficient_and_advanced, d.school_id AS school_id FROM dc_nclb_scores_2011 d JOIN sy_2010_2011_school_type sy on sy.school_id = d.school_id WHERE sy.school_group = 'public school'AND sy.school_type ='elementary' ORDER BY reading_percent_proficient_and_advanced DESC) as sub_table"

Pheonix · Accepted Answer · 2011-08-17 19:46:15Z

1

I did not see any JOIN keyword actually, Try This Maybe ?

SET @rank=0;
SELECT @rank:=@rank+1 as rank, 
       sy.formatted_school_name, 
       FLOOR(d.reading_percent_proficient + d.reading_percent_advanced) AS reading_percent_proficient_and_advanced 
FROM d_test_scores d JOIN sy_2010_2011_school_type sy on sy.school_id = d.school_id 
WHERE  sy.school_group = 'public school'
       AND sy.school_type ='elementary' 
ORDER BY reading_percent_proficient_and_advanced DESC

Just a guess.

answered Aug 17, 2011 at 19:46

Pheonix

6,0627 gold badges32 silver badges48 bronze badges

1 Comment

Laxmidi Over a year ago

Thank you so much for the help. I combined your join with Sam DeHaan's subquery idea and got it to work.

Collectives™ on Stack Overflow

Problem with MySQL Join

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related