skip to Main Content

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

3

Answers


  1. 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. 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. 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