Member Avatar for jen140

Hello all.
I have a small problem with sql.
I have a table that has name and rank.
For example the ranks would be getted like :
select name,score from scores order by score desc
So the first line i would see would be person with rank 1, second line with rank 2...
But how can i know in what line does the name appears ?
For example i want to know rank of the person named 'abc'.
Thanks in advance.

  • 3 Contributors
  • 5 Replies
  • 101 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by jen140

Dani AI

Generated

Short summary and practical options (building on , and ):

If the goal is "tell me what row/rank a given player appears at when the table is ordered by score descending", there are three reliable approaches depending on your MySQL version and how you want to treat ties.

Modern MySQL (8+): use window functions. ROW_NUMBER() gives a strict position, RANK() gives gaps for ties, and DENSE_RANK() gives contiguous ranks for ties.

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS pos
FROM leaderboard;

To get a single player's position, wrap that as an inline view and filter on the outer query. See MySQL window functions docs: MySQL window functions.

Older MySQL (pre-8): two common patterns.

  1. Correlated subquery (good for finding one player's rank without scanning/returning the whole table):

    SELECT l.player,
    (SELECT COUNT(*) FROM leaderboard x WHERE x.points > l.points) + 1 AS rank
FROM leaderboard l
WHERE l.player = 'target_user';

    This produces the conventional "rank" (ties share the same rank; next rank jumps). Use COUNT(DISTINCT x.points) if you want dense ranks.

  2. User-variable numbering (produce a full ordered list with row numbers):

    SET @r := 0;
SELECT player, points, (@r := @r + 1) AS pos
FROM (
SELECT player, points FROM leaderboard ORDER BY points DESC
) AS ordered;

    This assigns sequential numbers after ordering, but note it’s a MySQL-specific trick and can be fragile in more complex queries; see MySQL user variables.

Performance notes and caveats:

  • Index the score/points column for faster counts.
  • For a single-user rank, the correlated-count approach is usually the most efficient on large tables.
  • If using PHP (as considered), it’s fine for small result sets; for large leaderboards prefer the DB-side methods.
  • Decide how to handle ties up front (ROW_NUMBER vs RANK vs DENSE_RANK).

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

select name,score from scores where name = 'jen140'

Member Avatar for jen140

I think i expleined not well.
For example, i have the next output :
name score
me 1000
he 998
they 900
He is on line 2 (after ordering by score desc) how to know that its on line 2 ?

Member Avatar for pritaeas

Don't understand what you are trying to achieve.

Member Avatar for almostbob 
insert into #temptable ( IDENTITY(int, 1,1) AS number, select * FROM table ORDER BY score desc) SELECT * FROM #temptable ORDER BY number DROP TABLE #temptable

thought process only, cant guarantee the code
mysql is likely buggy its been a while
select the data you need into a temp table with an autoincrementing field
print the temp table
drop the temp table

Member Avatar for jen140

Ok, thanks for your responses, but after thinking a bit more, i thought that implementing that part in the php would be easier.
Thanks for your responses, gonna see if the temp querry trick will work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.