SQL Query get row rank or position on the main select query

Question

I'm trying to get the Rank of a specific row from my SELECT query.

Here's my table :

| PID   | Age   |
|------ |-----  |
| 5295  | 27    |
| 4217  | 26    |
| 2935  | 25    |
| 8706  | 24    |

My query and code: I'm using SQL Server

$x = 0;
$query = odbc_exec($connect, "SELECT * FROM mytable ORDER BY Age DESC"); // Ordering by "Age" Descending
while($row = odbc_fetch_array($query)) {
    $x++;
    if($row['PID'] == 2935) { break; // Stop }
}

echo $x; // output "3"

This method works, but the question is can this be done with simple standalone SQL Query instead of looping through all results then incrementing $x variable to get the specified row rank. because this table contains thousands of entries which will be a little laggy while looping till it find the desired row then stop looping.

The logic of this ranking since I order with Age descending, so for example it will start with 27 with rank 1 along to 24 with rank 4.

How can I achieve this?

Answer 1

You can use row_number() in a subquery to assign a rank to each record, then filter out in the outer query on the row you are interested in:

select *
from (
    select t.*, row_number() over(order by age desc) rn
    from mytable t
) t
where pid = 2935

In MySQL, window functions are supported in version 8.0 only. In SQL Server, they have been available for a long time.