postgrest retreive ranked results

Question

I made a game, with level and scores saved into an sql table like this :

create table if not exists api.scores (
id serial primary key,
pseudo varchar(50),
level int,
score int,
created_at timestamptz default CURRENT_TIMESTAMP
);

I want to display the scores in the ui with the rank of each score, based on the score column, ordered by desc.

Here is a sample data :

 id |  pseudo  | level | score |          created_at
----+----------+-------+-------+-------------------------------
  1 | test     |     1 |     1 | 2020-05-01 11:25:20.446402+02
  2 | test     |     1 |     1 | 2020-05-01 11:28:11.04001+02
  3 | szef     |     1 |   115 | 2020-05-01 15:45:06.201135+02
  4 | erg      |     1 |   115 | 2020-05-01 15:55:19.621372+02
  5 | zef      |     1 |   115 | 2020-05-01 16:14:09.718861+02
  6 | aa       |     1 |   115 | 2020-05-01 16:16:49.369718+02
  7 | zesf     |     1 |   115 | 2020-05-01 16:17:42.504354+02
  8 | zesf     |     2 |   236 | 2020-05-01 16:18:07.070728+02
  9 | zef      |     1 |   115 | 2020-05-01 16:22:23.406013+02
 10 | zefzef   |     1 |   115 | 2020-05-01 16:23:49.720094+02

Here is what I want :

 id |  pseudo  | level | score |          created_at           | rank
----+----------+-------+-------+-------------------------------+------
 31 | zef      |     7 |   730 | 2020-05-01 18:40:42.586224+02 |    1
 50 | Cyprien  |     5 |   588 | 2020-05-02 14:08:39.034112+02 |    2
 49 | cyprien  |     4 |   438 | 2020-05-01 23:35:13.440595+02 |    3
 51 | Cyprien  |     3 |   374 | 2020-05-02 14:13:41.071752+02 |    4
 47 | cyprien  |     3 |   337 | 2020-05-01 23:27:53.025475+02 |    5
 45 | balek    |     3 |   337 | 2020-05-01 19:57:39.888233+02 |    5
 46 | cyprien  |     3 |   337 | 2020-05-01 23:25:56.047495+02 |    5
 48 | cyprien  |     3 |   337 | 2020-05-01 23:28:54.190989+02 |    5
 54 | Cyzekfj  |     2 |   245 | 2020-05-02 14:14:34.830314+02 |    9
  8 | zesf     |     2 |   236 | 2020-05-01 16:18:07.070728+02 |   10
 13 | zef      |     1 |   197 | 2020-05-01 16:28:59.95383+02  |   11
 14 | azd      |     1 |   155 | 2020-05-01 17:53:30.372793+02 |   12
 38 | balek    |     1 |   155 | 2020-05-01 19:08:57.622195+02 |   12

I want to retreive the rank based on the full table whatever the result set.

I'm using the postgrest webserver.

How do I do that ?

Answer 1

You are describing window function rank():

select t.*, rank() over(order by score desc) rnk
from mytable t
order by score desc