Kysely•3mo ago

RANK() query for a leaderboard

Hi I am trying to get a user's ranking and I got the following query working: https://kyse.link/KRaFK I wanted to ask if this is the right approach to take, or if there is a way to avoid using the raw sql snippet from the link:

const userRanking = await db
  .selectFrom(
    db
      .selectFrom("user")
      .select([
        "id",
        "displayUsername",
        "totalDistanceWalked",
        sql<number>`RANK() OVER (ORDER BY "total_distance_walked" DESC)`.as(
          "rank",
        ),
      ])
      .as("ranked_users"),
  )
  .selectAll()
  .where("id", "=", userId)
  .executeTakeFirstOrThrow();

const userRanking = await db
  .selectFrom(
    db
      .selectFrom("user")
      .select([
        "id",
        "displayUsername",
        "totalDistanceWalked",
        sql<number>`RANK() OVER (ORDER BY "total_distance_walked" DESC)`.as(
          "rank",
        ),
      ])
      .as("ranked_users"),
  )
  .selectAll()
  .where("id", "=", userId)
  .executeTakeFirstOrThrow();

Solution:

You can use the agg method (aggregate) like this https://kyse.link/SpOaNe

Jump to solution

3 Replies

Solution

koskimas•3mo ago

You can use the agg method (aggregate) like this https://kyse.link/SpOaNe

UVOP•3mo ago

I seem to be having an issue with this link, it's stuck on loading

UVOP•3mo ago

Removing the e from the end of the link worked! Thanks for that solution, works perfectly

Gaming

Programming

RANK() query for a leaderboard

Did you find this page helpful?