1

I'm new to SQL(ite), so i'm sorry if there is a simple answer i just were to stupid to find the right search terms for.

I got 2 tables: 1 for user information and another holding points a user achieved. It's a simple one to many relation (a user can achieve points multiple times).

table1 contains "userID" and "Username" ...

table2 contains "userID" and "Amount" ...

Now i wanted to get a highscore rank for a given username. To get the highscore i did:

SELECT Username, SUM(Amount) AS total FROM table2 JOIN table1 USING (userID) GROUP BY Username ORDER BY total DESC

How could i select a single Username and get its position from the grouped and ordered result? I have no idea how a subselect would've to look like for my goal. Is it even possible in a single query?

ldr
  • 211
  • 1
  • 11
  • @CL., since you marked this as duplicate of [this](http://stackoverflow.com/questions/5682886/retrieve-rank-from-sqlite-table), can you explain how to use 'count(*)' with a query that's grouped and ordered? Otherwise Richard is right and i have to do it in the application or create a temp table. – ldr Dec 04 '15 at 15:18
  • The subquery is implicitly ordered by the comparison in the WHERE. For the grouping, use a correlated subquery. – CL. Dec 04 '15 at 16:54
  • Sadly i don't know / understand how to do this. Thats why i asked the question in the first place. Could you post a working code based on the data i've given, so i can understand what you are trying to hint me towards? I already feel stupid for not getting it on my own, so i'd really appreciate it. – ldr Dec 04 '15 at 17:59

2 Answers2

1

You cannot calculate the position of the user without referencing the other data. SQLite does not have a ranking function which would be ideal for your user case, nor does it have a row number feature that would serve as an acceptable substitute.

I suppose the closest you could get would be to drop this data into a temp table that has an incrementing ID, but I think you'd get very messy there.

It's best to handle this within the application. Get all the users and calculate rank. Cache individual user results as necessary.

Without knowing anything more about the operating context of the app/DB it's hard to provide a more specific recommendation.

Rich Seviora
  • 1,789
  • 12
  • 16
0

For a specific user, this query gets the total amount:

SELECT SUM(Amount)
FROM Table2
WHERE userID = ?

You have to count how many other users have a higher amount than that single user:

SELECT COUNT(*)
FROM table1
WHERE (SELECT SUM(Amount)
       FROM Table2
       WHERE userID = table1.userID)
      >=
      (SELECT SUM(Amount)
       FROM Table2
       WHERE userID = ?);
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks, as i feared my thought process was completely in the wrong direction. As a sidenote to the answer: This is pretty slow on big tables, so it's only suitable for small tables. – ldr Dec 05 '15 at 05:57
  • Then add an index on `Table2.userID`. – CL. Dec 05 '15 at 08:53