Show duplicate grades of each department with department name and their grades

Question

I can show how many duplicate scores exist, but I can't show in which department.

select distinct *
  , ROW_NUMBER() over (partition by f.depname order by f.stgrade) as ranked
from (
  SELECT COUNT(*) as repeatetive, DepName, stgrade
  FROM TBL_DEPARTMANTS
  CROSS JOIN TBL_GRADES
  group by stgrade, DepName
  having count(*) > 1
) as f

Answer 1

• Do not use CROSS JOIN which yields multiple rows to return.
• You do not need a subquery, use window analytic function directly in the main query.
• Remove partition by f.depname part within the row_number() over (..) expression.

So, the following query suits well for your need :

select DepName, stgrade,
       row_number() over (order by g.stgrade,d.DepName) as ranked
  from TBL_DEPARTMANTS d
  left join TBL_GRADES g
    on g.DeptId = d.Id
 group by stgrade, DepName
having count(*) > 1;

assuming TBL_GRADES table has a column called DeptId( Otherwise you'll need one more table to be added to this query with a JOIN statement )

Demo