Filter SQL by Aggregate Not in SELECT Statement-CodePudding

Can you filter a SQL table based on an aggregated value, but still show column values that weren't in the aggregate statement?

My table has only 3 columns: "Composer_Tune", "_Year", and "_Rank".
I want to use SQL to find which "Composer_Tune" values are repeated in each annual list, as well as which ranks the duplicated items had.
Since I am grouping by "Composer_Tune" & "Year", I can't list "_Rank" with my current code.
The image shows the results of my original "find the duplicates" query vs what I want:

Current vs Desired Results

I tried applying the concepts in this Aggregate Subquery StackOverflow post but am still getting "_Rank is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" from this code:

  WITH DUPE_DB AS (SELECT * FROM DB.dbo.[NAME] GROUP BY Composer_Tune, _Year HAVING COUNT(*)>1)
  SELECT Composer_Tune, _Year, _Rank    
  FROM DUPE_DB

CodePudding user response：

You need to explicitly declare the columns used in the Group By expression in the select columns. You can use the following documentation if you are using transact sql for the proper use of Group By.

CodePudding user response：

Simply join the aggregated resultset to original unit level table:

WITH DUPE_DB AS (
    SELECT Composer_Tune, _Year 
    FROM DB.dbo.[NAME] 
    GROUP BY Composer_Tune, _Year 
    HAVING COUNT(*) > 1
) 

SELECT n.Composer_Tune, n._Year, n._Rank 
FROM DB.dbo.[NAME] n
INNER JOIN DUPE_DB
  ON n.Compuser_Tune = DUPE_DB.Composer_Tune
  AND n._Year = DUPE_DB._Year
ORDER n.Composer_Tune, n._Year