Using a CTE in OVER(PARTITION BY)

Question

I'm trying to calculate volume from 3 columns in a table and return only unique volumes. We have many rows with the same Width, Height, and Length and so naturally my volume calculation will have duplicate return values for Volume. I am under the impression that, in order to accomplish this, I must use OVER, PARTITION and a CTE as aliases are not allowed to be referenced in OVER

WITH
cteVolume (Id, Volume)
AS 
(
    SELECT Id, Width * Height * [Length] AS Volume FROM  PackageMaterialDimensions
)
SELECT * 
INTO #volumeTempTable
FROM (
SELECT pp.ID, (pp.Width * pp.Height * pp.[Length]) AS Volume,
ROW_NUMBER() OVER(PARTITION BY cte.Volume ORDER BY pp.ID DESC) rn
FROM PlanPricing pp
INNER JOIN cteVolume cte ON pp.ID = cte.Id
) a
WHERE rn = 1

SELECT * FROM #volumeTempTable
ORDER BY Volume DESC

DROP TABLE #volumeTempTable  

Note, the reason for the temp tables is because I plan on doing some extra work with this data. I also am currently debugging so I am using these tables to output to the data window

Here is what is wrong with this query
- It is still returning duplicates
- It is only returning one volume for every row
- It is only returning about 75 rows when there are 71000 rows in the table

How can I modify this query to essentially do the following
- Calculate volume for EVERY row in the table
- SELECT rows with unique volume calculations. (I do not want to see the same volume twice in my result set)

Edit - providing data as requested

Current data set Ignore the extra columns

What I would like is
ID | Volume
193 | 280
286 | 350
274 | 550
241 | 720

Basically, I want to calculate volume for every row, then I would like to somehow group by volume in order to cut down duplicates and select the first row from each group

Answer 1

Does this do what you want?

WITH cteVolume (Id, Volume) AS (
      SELECT Id, Width * Height * [Length] AS Volume
      FROM PackageMaterialDimensions
    )
SELECT DISTINCT volume
FROM CTE ;

If you want one id per volume:

WITH cteVolume (Id, Volume) AS (
      SELECT Id, Width * Height * [Length] AS Volume
      FROM PackageMaterialDimensions
    )
SELECT volume, MIN(Id) as Id
FROM CTE
GROUP BY volume;