0

I'm making a raking system. I want get the line in one request.

Here is a DB fiddle of my complete example: https://www.db-fiddle.com/f/3Com2wnrhaqfiTKqJSZtft/0

In the example, the UUID used is 36619b04-4fbe-4ba9-8bd6-aaba7df74bb6. As the first query show, it should be 4. But the second query return 2. The +1 in the query is to start ranking at 1 instead of 0. The date_created column is for a second ordering (if same value, we check according to date). With this system, it force to have a unique rank (there is no equality).

Actually, with the first request, I get the ranking in my program with a simple increment. But, for the second query I can't as I want the rank of someone that could be the 12502th.

How can I fix it?

5
  • What version of MySQL? 8.0 has RANK() and DENSE_RANK().
    – Rick James
    Commented Apr 13, 2023 at 21:15
  • @RickJames I just updated. Through I were using mysql but no, it's mariadb
    – Elikill58
    Commented Apr 13, 2023 at 21:22
  • 2
    10.2 has those "windowing functions".
    – Rick James
    Commented Apr 13, 2023 at 23:10
  • @RickJames Before posting my question, I tried the RANK() method but everyone were at rank 1 (or I got syntax issue)
    – Elikill58
    Commented Apr 14, 2023 at 5:59
  • Let's see your code. Consider RANK()-1
    – Rick James
    Commented Apr 14, 2023 at 15:23

1 Answer 1

1

You could use ROW_NUMBER window function.

Consider the following data example.

CREATE TABLE test(
  id INT AUTO_INCREMENT,
  uuid VARCHAR(36),
  stats INT,
  date_created TIMESTAMP,
  
  PRIMARY KEY (id)
);
INSERT INTO test(uuid, stats, date_created) VALUES
('97cc165a-0a2e-495e-a99c-7df93e1f1e27', 10, TIMESTAMP('2023-05-24')),
('064c3448-973b-4794-b953-8a862787b971', 8, TIMESTAMP('2023-05-24')),
('36619b04-4fbe-4ba9-8bd6-aaba7df74bb6', 5, TIMESTAMP('2023-05-24')),
('377b8f52-ce94-4dac-baf8-9e07c6e58ae9', 5, TIMESTAMP('2023-04-23')),
('2c4e0f40-078a-4bd6-a405-82813d4cb569', 5, TIMESTAMP('2023-06-28'));

Query:

select  id, uuid, stats, date_created, rn
from (select *,
              row_number() over (order by stats desc, date_created desc ) rn
       from test
) x
where uuid ='36619b04-4fbe-4ba9-8bd6-aaba7df74bb6';

Result:

id uuid stats date_created rn
3 36619b04-4fbe-4ba9-8bd6-aaba7df74bb6 5 2023-05-24 00:00:00 4
2
  • Oh, it's necessary to make a sub query? Does it impact performances to run over 10k lines?
    – Elikill58
    Commented Apr 14, 2023 at 11:59
  • @Elikill58 it's necessary to make a sub query? , yes it is. With proper indexes you shouldn't have any problem. Commented Apr 14, 2023 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.