Extract one row from one account id from a list, part 2

Question

Not sure how to ask about next steps in SO, but this refers to an earlier question: Extract one row from each account id from the list

I am working with a request:

SELECT *
FROM scores s1
WHERE accountid NOT IN (SELECT accountid FROM scores s2 WHERE s1.score < s2.score)
ORDER BY score DESC

This selects the top scores and limits the results to one line per account; their top score.

The last hurdle is that this query returns multiple rows for accounts that have multiple occurrences of their top score. Therefore, if account 17 has ratings of 40, 75, 30, 75, the query returns both rows with a quantity of 75.

Can someone modify this query (or provide the best one) to fix this case and really limit it to one line per account ID?

Thanks again!

Answer 1

If you are only interested in the account and account, you can use the simple GROUP BY query above.

SELECT accountid, MAX(score) 
FROM scores 
GROUP BY accountid;

If you need other attributes from the rating table, you can get other attributes from the query string of the following form:

SELECT s1.*
FROM scores AS s1
  LEFT OUTER JOIN scores AS s2 ON (s1.accountid = s2.accountid 
    AND s1.score < s2.score)
WHERE s2.accountid IS NULL;

But this still gives a few lines, in your example, where this account has two accounts corresponding to its maximum value. To further reduce the result given in one line, for example, the line with the last player, try the following:

SELECT s1.*
FROM scores AS s1
  LEFT OUTER JOIN scores AS s2 ON (s1.accountid = s2.accountid 
    AND s1.score < s2.score)
  LEFT OUTER JOIN scores AS s3 ON (s1.accountid = s3.accountid 
    AND s1.score = s3.score AND s1.gamedate < s3.gamedate) 
WHERE s2.accountid IS NULL
    AND s3.accountid IS NULL;

Answer 2

select accountid, max(score) from scores group by accountid;

Answer 3

RDBMS , , .

select ...
from   (
   select accountid,
          score,
          ...
          row_number() over 
               (partition by accountid
                    order by score desc) score_rank
   from   scores)
where score_rank = 1;

, , , , ( desc, test_date desc), .

, , .

, , , , :

select ...
from   (
   select accountid,
          score,
          ...
          max(score) over (partition by accountid) max_score
   from   scores)
where score = max_score;

Answer 4

, DISTINCT .

SELECT DISTINCT UserID, score
FROM scores s1
WHERE accountid NOT IN (SELECT accountid FROM scores s2 WHERE s1.score < s2.score)
ORDER BY score DESC

Answer 5

? x y?

Answer 6

MS SQL, .

SELECT *
FROM scores
WHERE scoreid in
(
  SELECT max(scoreid)
  FROM scores as s2
    JOIN
  (
    SELECT max(score) as maxscore, accountid
    FROM scores s1
    GROUP BY accountid
  ) sub ON s2.score =  sub.maxscore AND s2.accountid = s1.accountid
  GROUP BY s2.score, s2.accountid
)