Question 58197 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL: How to select single rows position – Mysql

Let’s say I have a table that has 3 fields: (id, name, points).

Now initially I want to order them by points and also get the position. So here’s my current query that does this:

SELECT
    id,
    name,
    points,
    ROW_NUMBER() OVER (ORDER BY points DESC) AS position
FROM
    mytable;

This now works, and I get the following result set:

enter image description here

Here’s where it gets tricky. Let’s say I’m interested in specific row. For example I am interested in EnglishRose. I would love to know what position it has based on the points.

I can’t just return the whole set and then iterate until I find this row because this table is massive (let’s say we are talking about 100,000+ records. I am trying to find most optimal solution for this. Any ideas? Keep in mind any solution that is working should hopefully also be optimal. I am asking because probably some non optimal sub selects can be done

Any ideas by MySQL experts? Btw my mysql version is >8

Tags:

3

Answers


  1. 0

    If you know the specific element by name that you want to isolate you can use the WHERE statement like so:

    WHERE (name = ‘EnglishRose’)

    (Im not to sure if this is 100% correct since its been a while since i wrote SQL :-))

    I hope this helps!

    Login or Signup to reply.
  2. 0

    Have a look at CTE : https://dev.mysql.com/doc/refman/8.0/en/with.html

    They allow you to treat a query as a local table.

    You could do something like

    WITH
localPositionning AS (
SELECT
  id,
  name,
  points,
  ROW_NUMBER() OVER (ORDER BY points DESC) AS position
FROM
  mytable
  )
SELECT * FROM localPositionning WHERE ...
    Login or Signup to reply.
  3. 0

    The records will not all be scanned, but only the ones with points that are higher than the one you’re looking for:

    WITH CTE AS (
  select *, ROW_NUMBER() OVER (ORDER BY points DESC) AS position
  from mytable
  where points >= (
    select points
    from mytable
    where name = 'b'
  )
)
select max(position) as position from CTE;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search