How can I get rows from a table with the max value (all equal rows) for each different value of anot-CodePudding

In an Android App I'm developping, I need to get info on a table depending on 2 rows (so with pairs) but can't really find a way to do it. How could I achieve that with SQL ?

/!\ The following is a simplified example as the real situation would be more confusing than it would help. /!\

So for exemple, if I have this table:

How could I get this result (the rows for the best scores for each category) :

I used to do it like this:

SELECT name, category, MAX(score)
FROM SCORE_TABLE
GROUP BY category

But I'd get this :

When I checked for answers I saw solutions using sub-queries like so:

SELECT *
FROM SCORE_TABLE
WHERE score == (
    SELECT MAX(score)
    FROM SCORE_TABLE
)

But this would only work with one category, so I'm thinking maybe I could use some variation of this using a table with all the category-max_score pairs, like this:
MAX_SCORE_TABLE:

But I really don't see how, so if you have any ideas, I'd love some help ^^

CodePudding user response：

I think this should work, but pls check on the larger dataset:

SELECT name,
       category,
       score
FROM score_table AS tab1
RIGHT JOIN
  (SELECT category AS cat,
          MAX(score) AS max
   FROM score_table
   GROUP BY cat) AS tab2 
ON tab1.score = max AND category = cat;

CodePudding user response：

I believe what you need is the NTILE(num) function. It divides your data into buckets based on partition. Your top 2 data from each category will get ranked 1 in each category.

SELECT name, category, score, NTILE(2) OVER (
        PARTITION BY category
        ORDER BY score DESC
    ) scoreplacement
FROM SCORE_TABLE
where NTILE(2) OVER (
        PARTITION BY category
        ORDER BY score DESC
    ) = 1
GROUP BY category

I haven't tried this by creating a table but this should help. Since From is evaluated before select we can't refer to the alias column name.