+----+------+
| id | col1 |
+----+------+
|  1 | abc  |
|  2 | def  |
|  3 | ghi  |
|  4 | jkl  |
|  5 | mno  |
+----+------+

select
    id,
    col1,
    row_number() over(order by col1 desc) as row_num
from #table

with NumberedRows as
(
    select id, col1,
        row_number() over(order by col1 desc) as row_num
    from #table
)
select id, col1, row_num
from NumberedRows
where col1 = 'jkl'