How can I get a valid rank counter?

Question

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?

score 1 · Accepted Answer · edited Apr 14 '23 at 12:31

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

How can I get a valid rank counter?

1 Answers1

Linked