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.
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