query within query

[Eric]

Hello,

I have a query that I've gotten to work fine in SQL Server, but when I
tried to run the same query on an indentially structured database in
MySQL, I get a syntax error. This is the query:

select user_id, score, (select count(*) + 1
from ericTest
where score > A.score) as rank from ericTest A

Essentially, it's a table of user_id, score, and the user's rank,
which is calculated by the inner query. Thoughts on what's wrong?

Thanks!

~ Eric

[Aggro]

Eric wrote:

select user_id, score, (select count(*) + 1
from ericTest
where score > A.score) as rank from ericTest A

Subqueries are supported from MySQL version 4.1 so I'm assuming you are
using older version of MySQL server.

[Bill Karwin]

Eric wrote:

I have a query that I've gotten to work fine in SQL Server, but when I
tried to run the same query on an indentially structured database in
MySQL, I get a syntax error. This is the query:

select user_id, score, (select count(*) + 1
from ericTest
where score > A.score) as rank from ericTest A

Essentially, it's a table of user_id, score, and the user's rank,
which is calculated by the inner query. Thoughts on what's wrong?

As Aggro pointed out, MySQL only supports subqueries as of version 4.1,
which just this week has been released out of beta/gamma status (hooray!).

Try this as an alternative that uses no subquerie:

SELECT A.user_id, A.score, COUNT(B.score) + 1 AS rank
FROM ericTest A LEFT OUTER JOIN ericTest B ON (B.score > A.score)
GROUP BY A.user_id

Regards,
Bill K.