CODEWITHSUNDEEP
mysqlselectphpmyadmin
BackSlash
BackSlash

Reputation: 22233

mysql - selecting from result

I have a table

scores(user, score)

and I have this query

SET @row_num = 0; 
SELECT @row_num := @row_num + 1 as row_index, user, score 
FROM scores ORDER BY score DESC

now I want to select, from this query result, the user with name 'john' and score '1400' to know what is his row_index, something like

SELECT row_index 
FROM *result* WHERE user='john' AND score=1400

how do I do that? I tried

SET @row_num = 0; 
SELECT row_index 
FROM (SELECT @row_num := @row_num + 1 as row_index, user, score 
      FROM scores ORDER BY score DESC)      
WHERE user='john' AND score=1400`

but phpMyAdmin says 

#1248 - Every derived table must have its own alias

How can I do that?

Thank you, Alessandro

Upvotes: 1

Views: 105

Answers (4)

Jirilmon
Jirilmon

Reputation: 1944

Try this:

SET @row_num = 0; 

SELECT row_index FROM (SELECT @row_num := @row_num + 1 as row_index, user, 
score FROM scores ORDER BY score DESC) AS alias1 WHERE user='john' AND score=1400

Upvotes: 1

Justin
Justin

Reputation: 9724

Query:

SET @row_num = 0; 
SELECT a.row_index 
FROM (SELECT @row_num := @row_num + 1 as row_index, 
             user, 
             score 
             FROM scores ) a
WHERE a.user='john' AND a.score=1400
ORDER BY a.score DESC

I think Ordering in Subquery not always allowed.

Upvotes: 2

declonter
declonter

Reputation: 321

Try to use views.

SET @row_num = 0; 
CREATE VIEW v AS SELECT @row_num := @row_num + 1 as row_index, user, score FROM scores ORDER BY score DESC;
SELECT row_index FROM v;

Upvotes: 1

Kermit
Kermit

Reputation: 34055

You haven't added an alias to the derived table.

SELECT row_index FROM (...) AS alias
WHERE alias.user = 'john' AND alias.score = 1400

Upvotes: 2

Related Questions