Greatest-n-per-group; multiple ways of handling ties?

Question

Quite often I have to find a MAX/MIN value within a group. There are numerous solutions to this problem, but one thing they don't seem to account for is handling ties.

For example, take a dataset with the following records:

category   | product      | profit 2018  | profit 2017 | descr    
-------------------------------------------------------------
bicycles   | mountainbike | 10000        | 10000       |  ~~~~ 
bicycles   | blue bicycle | 12000        | 12000       |  ~~~~
bicycles   | unicycle     |  6500        |  7000       |  ~~~~
bicycles   | red bicycle  | 12000        | 14000       |  ~~~~
accessories| light        |  5000        |  3500       |  ~~~~
accessories| water bottle |  3200        |  2500       |  ~~~~
parts      | wheel        |  4000        |  4000       |  ~~~~
parts      | saddle       |  4000        |  3000       |  ~~~~

If I want to find the most profitable product over 2018 per category (solved here), I'll get multiple rows per category because the blue and red bicycle are tied, and so are the wheel and saddle. Say I only want one result (for whatever reason).

I know of 2 ways to handle this:

Use ROW_NUMBER () OVER (PARTITION BY ... ORDER BY ...) and wrap the query in a CTE, then selecting WHERE ROW_NUMBER = 1. What's nice about this solution is that I can use a tiebreaker in the order by, for example, I could use profit 2017 as a tiebreaker
Selecting Top 1. This solution just gives a random result?

Is there a better way of handling ties in a situation like this?

you could use an order by / tiebreaker also on solution n.2 - but the problem doesn't seem really technical but logical .. if there's a tie, which row you want to return? — fthiella, Jul 20 '18 at 08:42

score 2 · Accepted Answer · answered Jul 20 '18 at 10:36

First, your table has some identity column. Then you can simplify the logic as :

select t.*
from table t
where identity_field = (select top (1) t1.identity_field 
                        from table t1
                        where t1.category = t.category
                        order by t1.profit_2018 desc
                       );

Greatest-n-per-group; multiple ways of handling ties?

1 Answers1