Keep in mind that
Group By
happens before Aggregation OVER PARTITION BY.
So the best way is to only use one of them at a time
WITH Agg AS (
SELECT x
,y
,z
,name
,number
,count(*) OVER (PARTITION BY x,y,z ORDER BY ?) AS count_
,ROW_NUMBER() OVER (PARTITION BY x,y,z ORDER BY ?) AS rn
FROM locations AS ml
JOIN locs AS ma ON ma.locid = ml.locid
)
SELECT x
,y
,z
,name
,number
,count_
FROM Agg
WHERE rn = 1
Adjust as needed.
And keep in mind that you always need a field(s) to order by when you partition