Greetings. There was such a task: to bring the first 3 users, whose rating is the highest and the position of the user in this sorted list, that is, there is a user Вася and his рейтинг = 3000 , there are also users Коля , Дима , Петя , whose ratings are respectively 10 000 , 8 200 and 6000 .

The first 3 users with high ratings can be obtained as follows:

 SELECT user_id FROM users ORDER BY rating DESC LIMIT 3

It turns out like this:

 1. Коля 2. Дима 3. Петя ... n. Вася

The question is: is it possible to somehow display the first 3 users and the position n the user himself, with respect to their rating, using a single query?

There were solutions using a loop and a counter, but it seems to me that this is not the best option or create a separate table. What do you think?

    2 answers 2

    Generally speaking, the answer will depend on the SQL dialect used. For example, in SQL Server (starting with version 2008) you can do something like this:

     ;WITH cte AS ( SELECT ROW_NUMBER() OVER (ORDER BY rating DESC) AS rn, name FROM users ) SELECT TOP 3 rn, name FROM cte UNION SELECT rn, name FROM cte WHERE name = 'Вася' ORDER BY rn
    • one
      And when I wrote, I did not see the comment that it was about MySQL (although I should have guessed from LIMIT). - Yaant
     SELECT t3.name, t3.place FROM ( SELECT t1.name, COUNT(t2.rating) place FROM table t1, table t2 WHERE t1.rating <= t2.rating GROUP BY t1.name ) t3 WHERE t3.place < 4 OR t3.name = 'Вася'