is there a Sql function that aggregate on custom terms

Question

Suppose I have a table like this

category_1	category_2	ranked_class	random_info
A	1	'boss'	[more data]
A	1	'director'	[more data]
A	2	'boss'	[more data]
A	2	'employee'	[more data]
B	3	'employee'	[more data]
C	1	'client'	[more data]
C	1	'other_client'	[more data]
C	2	'director'	[more data]

I want to aggregate base on "term - priority" something like

SELECT term_priority(ranked_class, 'boss'>'director'>'employee')
GROUP BY category1,category2

my objective is to have a resulting table that has no duplicated (category1 an category2) but the method of selection is based on a customized rank.
and to get the extra_columns after.

P.S when the aggregation contains no ranked term, it should not aggregate

(in other terms, I want to delete specific duplicated rows, based on ranked terms)

category_1	category_2	ranked_class	random_info
A	1	'boss'	[more data]
A	2	'boss'	[more data]
B	3	'employee'	[more data]
C	1	'client'	[more data]
C	1	'other_client'	[more data]
C	2	'director'	[more data]

(this following edit is to explicit the difference between my question and this one) The issue was I needed a way to remove duplicates based on a Rank, my approach was via aggregation, the other author had a ordering issue, even the "custom ordering" solution being applied to both cases, mine does not work without the "partition and selecting 1st row" strategy, in other words, even the solution to both cases being almost the same, the issues that originated them were from a different nature.

I tried to mantain the tables as tables, but Stack overflow did not let me post it that way so I had to turn all tables into code, hope they remain understandable — daniel_dutra, Sep 06 '22 at 14:42

score 2 · Accepted Answer · answered Sep 06 '22 at 14:48

There's no such aggregate function in TSQL, but you can do this with an windowing function, eg

with q as
(
  SELECT *, 
    row_number() over (partition by category1,category2 
                       order by case ranked_class when 'boss' then 1 
                                                  when 'director' then 2 
                                                  when 'employee' then 3
                                                  end ) rn
)
select category1,category2, ranked_class
from q
where rn = 1

is there a Sql function that aggregate on custom terms

1 Answers1