CODEWITHSUNDEEP
phpmysqlsql
Nano
Nano

Reputation: 58

SQL Statement for multiple request on the same table

I want to select multiple values from a database table. First request is to get the first ten values from a table for my top 10 list. Second request is to get the player's position in the table.

Pseudo-statement may be look like:

SELECT * FROM mytable ORDER BY score DESC LIMIT 10 AND WHERE username = myusernamevariable

Form this select I need to get the position of the second request (AND WHERE username = myusernamevariable)

Is there a way to create an SQL-Statement that returns the top ten database entries and the entry from a specific line (and the position where this entry is)?

Upvotes: 0

Views: 523

Answers (2)

Mohammad Masoudian
Mohammad Masoudian

Reputation: 3501

to get player position : 

SELECT COUNT(id)+1 AS position FROM mytable
WHERE score > (SELECT score FROM mytable WHERE username = 'myusernamevariable')
ORDER BY score DESC LIMIT 10 ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Do you want the position or to identify the row where the player is? You can easily add a flag to identify the player's row:

Select t.*, (username = myusernamevariable) as IsMyPlayer
FROM mytable t
ORDER BY score DESC
LIMIT 10 ;

If you want to return both one player's position and rows, then you would need two queries, because the structure of the return set is different. If you want the rank on each row, you can do that using:

Select t.*, (username = myusernamevariable) as IsMyPlayer, (@rank := @rank + 1) as rank
FROM mytable t cross join
     (select @rank := 0) const
ORDER BY score DESC
LIMIT 10 ;

Upvotes: 2

Related Questions