Question 113938 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get the position of a record

This might be a dumb and simple question, but since I am a completely newbie I could not find a workaround.

Lets say I have these users in the DB :

Me with maxScore : 4
John with maxScore: 3
Doe with maxScore: 9
Ana with maxScore: 12
Bob with maxScore: 1

I would like to get "3" as a result since after ordering the users, "Me" is the third one with 4 maxScore. BUT if the app has lets say 2000 users, I want to get the ranking of "Me", for example 1375.

How can I achieve this?

thanks in advance

I have been able to sort my DB with

  const usersAround = await prisma.user.findMany({
    orderBy: {
      maxScore: "desc",
    },
    cursor: {
      id: myUser.id,
    },
    take: 5,
  });

But I want to get the index (or the rank) of my user in the ranking of the scores.

Tags:

2

Answers


  1. 0

    Here you go:

    const users = await prisma.user.findMany({
  orderBy: {
    maxScore: "desc",
  },
  take: 5,
});

const ranking = users.findIndex(user => user.id === myUser.id) + 1
    Login or Signup to reply.
  2. 0

    You can use the Raw Query to pass a SQL query to find your global position.

    This Stack Overflow answer provides the sql query, which you can pass in the $queryRaw function to get the position.

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

Back To Top
Search