Hello,

I've looked in the documentation and on this forum and evertytime I get into stuff I really think is overkill for what I want (projections, views, etc..)

The thing is really simple ... I have a table with highscores for a game. It includes the gamers name and score (among other things). Everytime a gamer submits a score, a new record gets inserted (no updates). Now I want to get a query for the top 20 scores, but distinct it per user. So in fact, the query only returns the highest score for each player (and TOP 20'd) So something like : SELECT TOP 20 score, DISTINCT playername FROM SCORES ORDER BY score DESC

So if for instance we have player A with scores of 500 & 600, and player B with scores of 700 & 800, it would return : B-800 and A-600

What is the best (simplest & most performant) way to write this in self-servicing llblgen code ? This seems so simple, and I just can't get around it ..I just know I'm gonna feel so stupid when my eyes are opened ...

Btw, this is geared towards LLBLGen 2.6 Self servicing

SELECT TOP 20 score, DISTINCT playername FROM SCORES ORDER BY score DESC

DISTICNT works on the entire row, not on a field.

What you need is:

SELECT TOP 20 MAX(score), playername FROM SCORES
GROUP BY playername 
ORDER BY MAX(score) DESC

Hint: use a DynamicList

Hi,

thnx for the reply. I was indeed looking at the group by statement and dynmiclists, but it's still not completely what I want. I need to fetch some other fields too together with the score and playername (like the id of the record), and then I get a message that those other fields are not into the group by or an aggregate.

Please post the table DDL, and the complete select list you want to return.

The DDL: TABLE Scores Id (int, pk, identity) Score (int) Nickname (varchar(50)) Email (varchar(100),nullable) Timestamp (Datetime)

What I need is a top 20 list of ScoreEntity's (all fields filled in) with for each player his highest score, in order of descending score and descending timestamp (so if two players got the exact same score, the first one who got it is the winner of the two)

What I need is a top 20 list of ScoreEntity's (all fields filled in) with for each player his highest score, in order of descending score and descending timestamp (so if two players got the exact same score, the first one who got it is the winner of the two)

So you should use Ascending for the timestamp, not Descending.

Check this out: what if I have scored the top 3 highest scores? Would you still bring my name only ones in the TOP 20 list?

If the answer is no, then maybe you don't need to group by the user and just Order By the Score DESC and the timestamp ASC, and pick the TOP 20.

If the answer is Yes, then why do you keep my other scores, rather than only keeping my highest?

Oops my mistake, indeed the timestamp is ASC instead of DESC ...

But my main point stays ...If you have scored the top 3 score, your name should only be once in the list (on nr1). So I really need to have the top scores of each player, and then list those.

It's a bit like those Bejeweled games on facebook ...the highscore list only shows your highest score, not the "previous scores" (because in that case, it could be possible that the list only exists of one name, and that's exactly what we don't want).

Your last line gave me a good idea : If the answer is Yes, then why do you keep my other scores, rather than only keeping my highest? I will add a bitfield to the scores to invalidate the previous scores when a higher score is added . Then I can indeed just query the top 20 (where valid=true) !

I was blinded and this different approach is probably a more elegant solution

Thank you !