1

I have a sqlite3 table like this:

sqlite> select * from scores;
team        Name        score
----------  ---------   ----------
A           Name1       93
A           Name2       96
A           Name3       78
A           Name4       82
B           Name5       83
B           Name6       30
B           Name7       99
B           Name8       71
B           Name8A      45
B           Name8C      70
c           Name9       87
c           Name10      87
c           Name11      81
c           Name12      71
c           Name13      91

There are many teams (about 30 teams), each team has many members (more than 10,000 records in real). I just wanna got the First N winners of each team like this (for simplification, N=3 in this example):

A     Name2   96
A     Name1   93
A     Name4   82
B     Name7   99
B     Name5   83
B     Name8   71
C     Name13  91
C     Name9   87
C     Name10  87

and they are ranked by their score withing the same team.

how to get this result using sqlite3 query? anyone can give some clue? thanks very much.

btw, the 'Name' field are NOT unique, only (team, name) is UNIQUE, that means same name can appear in different team.

CL.
  • 173,858
  • 17
  • 217
  • 259
user2545464
  • 191
  • 1
  • 8

3 Answers3

0

You may try this one

set @currcount=0,@currvalue='', @N=4;
SELECT team, Name, score FROM ( SELECT team, Name, score, @currcount :=    
IF(@currvalue = team, @currcount + 1, 1) as rank, @currvalue := team FROM 
scores ORDER BY team DESC) as what WHERE rank<@N order by team asc, score desc
Riad
  • 3,822
  • 5
  • 28
  • 39
Akhter Al Amin
  • 852
  • 1
  • 11
  • 25
0

According Question: how to select first N row of each group?,Gordon Linoff's method, I figured out this code:

select * from dayobs a 
     where a.rowid in 
     (select b.rowid from dayobs b where b.station==a.station order by b.rain desc limit 5);

It works, but very very slow, sqlite gives every record more than 1 minute! this query will take about half hour to complete. Obviously, this cannot happen in real job. I think there must be some way to do it more efficient.

Community
  • 1
  • 1
user2545464
  • 191
  • 1
  • 8
0

Instead of select the top N result of each teams from the scores table, we can select the result set from a table, that contains the top N result of each teams.

Let's call the table having the data we need as leaderboard, which has the structure equal to scores.

We duplicate the data, which was inserted to score, to leaderboard. Then we only keep the best N scores of each team in leaderboard be deleting any scores not high enough from it. In order to do so, we need a trigger as follow:

CREATE TRIGGER "update_top_scores"
AFTER INSERT ON scores  
BEGIN
    -- Add the record to leaderboard
    INSERT INTO leaderboard (team, name, score) VALUES (NEW.team, NEW.name, NEW.score);
    -- only keep the top 30 records of each teams 
    DELETE FROM leaderboard WHERE team = NEW.team AND name NOT IN (SELECT name FROM leaderboard WHERE team = NEW.team ORDER BY score DESC LIMIT 30);

END

So now we can get the top N scores from each team using one simple query

SELECT * FROM leaderboard;

Assume that you don't want to have a new table or trigger in your db file, I suggest you to do the followings

  1. create new table leaderboard and dummy_scores
  2. create the trigger for dummy_scores
  3. use INSERT INTO ... SELECT statement to copy data from score to dummy_score, which activates the trigger too
  4. get the result from SELECT * FROM leaderboard
  5. drop leaderboard, dummy_scores and the trigger from db file

Now you have the original db file and the result you need.

Tse Kit Yam
  • 173
  • 8