CODEWITHSUNDEEP
mysql
Charles Bryant
Charles Bryant

Reputation: 1015

MySQL query on a generated result set

I am not sure what term I need to be searching for, but I could not find anything.

I am creating a MySQL query, ordering by high score and adding a row count column to return the position of the user, now each user has a column saying if they want to receive an email or not which would be emails = 1, but I need to first get my row count column as this is not stored. Obviously I could do this with server side scripting but it would be great to do a query on the result set generated.

set @N = 0; # MySQL returned an empty result set (i.e. zero rows).
SELECT email, @N := @N + 1 AS position, overallScore FROM `quizUsers`
ORDER BY overallScore DESC
LIMIT 0, 3000

So that is my base query, is there now a way to say 

WHERE emails = 1

Upvotes: 1

Views: 5413

Answers (3)

Bohemian
Bohemian

Reputation: 425043

You need to use the calculated value on an aliases result of the order by and have your WHERE clause on the inner query.
Try this:

set @N = 0;
-- Note calculation is made in the outer query, ie *after* ordering 
SELECT email, overallScore, @N := @N + 1 AS position 
FROM (
    SELECT email, overallScore, emails
    FROM quizUsers
    ORDER BY overallScore DESC) x -- Note ORDER BY is on the inner query
WHERE emails = 1
LIMIT 0, 3000

Upvotes: 1

Brent Baisley
Brent Baisley

Reputation: 12721

You can SELECT on a SELECT, sometimes called a subquery. MySql will call it a "derived table" in an explain of the query. Just wrap the SELECT in parenthesis and name it. Then you can treat the SELECT like it is a real table.

SELECT * FROM (SELECT email, @N := @N + 1 AS position ...) AS rankings WHERE emails=1

You will end up with less than 3000 records, but the positions will be all correct.

Upvotes: 1

Derk Arts
Derk Arts

Reputation: 3460

Did you try: 

SELECT email, @N := @N + 1 AS position, overallScore FROM `quizUsers` WHERE emails = 1

ORDER BY overallScore DESC

LIMIT 0, 3000

I think you want the rowcount of all users and then extract the ones with email=1 right? Then you would need a sub query I think, something like:

Select * FROM (SELECT email, @N := @N + 1 AS position, overallScore FROM `quizUsers` WHERE emails = 1

ORDER BY overallScore DESC) as T1 Where T1.emails = 1

LIMIT 0, 3000

Upvotes: 0

Related Questions