SELECT t.*, DENSE_RANK() OVER (ORDER BY department desc, country desc) AS grp FROM mytable t
Here is what the above code is Doing:
DENSE_RANK() function is used to assign a group number to each row. The
OVER clause specifies the columns to use to determine the groups. In this case, the groups are determined by the
country columns. The
ORDER BY clause specifies the order in which the groups are assigned. In this case, the groups are assigned in descending order by
department and then by
SELECT clause specifies the columns to return. In this case, all columns are returned.
FROM clause specifies the table to use.