Mysql query from ordered query

Question

I've done some research and ended up with this query.

SET @row_num = 0;
SELECT
   @row_num := @row_num + 1 as row_number
  ,id
  ,maxPoints
FROM users
ORDER BY maxPoints

...that gives me indexes of table ordered by maxPoints. And now comes the query for smarter people...

How do I get the row_number WHERE id = $i AND ... (other parameters) from this result.

Is it possible to do it by one composed query or do I need to split those queries on php side? I'm not able to put it up syntactically.

juergen d · Accepted Answer · 2017-09-16 16:22:25Z

3

Just put it in a subquery and select from that

select row_number
from
(
    SELECT id, @row_num := @row_num + 1 as row_number
    FROM users
    CROSS JOIN (select @row_num := 0) r
    ORDER BY maxPoints
) tmp
where id = $id

BTW you can init the @row_num variable with a subquery too, like I did in my query

answered Sep 16, 2017 at 16:22

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Ouhrabka Over a year ago

Piece of cake for you, huh? Thank you. Works like a charm. I'll be working on my skills.

Collectives™ on Stack Overflow

Mysql query from ordered query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related