MySQl : How to Exclude Row in a Ranking Sub-query

Question

Hi,

I've got a mySQL statement that selects some data and also ranks.

I want to have the record for 'Bob', for example, selected but NOT included in the ranking. So, I need Bob's row returned in the main select statement, but I need Bob excluded from the sub-SELECT which handles the ranking. I needs Bob's data but, he should not be counted in the rankings.

I tried AND t.name !='Bob' after WHERE x.category = t.category But, that's not working.

SELECT     t.name,
           t.category,
           t.score1,
           t.score2,
    (SELECT COUNT(*) 
    FROM my_table x
    WHERE x.category = t.category
    AND (x.score1 + x.score2) >= (t.score1 + t.score2)
    ) AS rank 

    FROM my_table t ORDER BY rank ASC

Any suggestions?

Thank you.

-Laxmidi

Answer 1

You should probably use AND x.name != 'Bob' instead of t.name.