I am using SQL Server.

I have a table like this:

and I am looking for this:

In other words, I need to group by the key and get the rule name that matches the smallest value of the Rule_Order.

At first, I was thinking about this:

select [Key], [Rule_Name]
from (
  select [Key],
    min([Rule_Order]),
    min([Rule_Name])
  from 
    mytable
  group by [Key]
)

which works with the example above, however the min([Rule_Name]) will look for the Rule_Name that comes first in alphabetic order.

If I change the rule order like this:

then the query above will give me this:

which is not what I want, as VAT is associated to the smallest rule order.

I understand that the word 'interco' comes before 'VAT' in the alphabet. I naively thought that order of the aggregators would be important in the group by: listing first rules of smallest order with min([Rule_Order]) THEN retrieving the rule names associated with this order. In my case, because there is alway one rule associated with a given order, I thought that using min or max would not be important. But it turned to be wrong.

So how should the query look like in that case?

Thanks!

CodePudding user response：

We can use row_number in a sub-query or a cte.

create table t(
Key_ varchar(10),
Rule_Name varchar(10),
Rule_Order int);
insert into t values
('Key1','interco',12),
('Key1','interco',12),
('Key1','VAT',10),
('Key1','interco',12),
('Key1','VAT',10),
('Key1','VAT',15),
('Key2','test',5);

select
  key_,
  rule_name
from 
(select
  key_,
  rule_name,
  row_number() over 
      (partition by key_
      order by rule_order) rn
from t) as sq
where rn = 1;
GO

key_ | rule_name
:--- | :--------
Key1 | VAT      
Key2 | test     

db<>fiddle here