csehz
asked on
SQL - DESC ordering in ROW_NUMBER
Dear Experts,
Could you please have a look on the attached small query, basically I would like just assign a number 1 by ROW_NUMBER to that region settings, which is set most often in SAP.
In the example concretely - the city 'Hamme' in Belgium has 7 times set '08', while 2 times '02', so would expect that in the MostFrequentSettings column the 1 should be beside of the '08' line.
But as you can see orange marked somehow that works opposite, could you please advise why and how it could be corrected?
Thanks in advance,
Could you please have a look on the attached small query, basically I would like just assign a number 1 by ROW_NUMBER to that region settings, which is set most often in SAP.
SELECT
ROW_NUMBER() OVER (PARTITION BY #RegionsCheck.CountryCity ORDER BY COUNT(#RegionsCheck.CountRegions) DESC) AS MostFrequentSettings,
#RegionsCheck.Country,
#RegionsCheck.City,
#RegionsCheck.CountryCity,
#RegionsCheck.Region,
#RegionsCheck.CountRegions
INTO
#RegionsCheckMostFrequent
FROM
#RegionsCheck
GROUP BY
#RegionsCheck.Country,
#RegionsCheck.City,
#RegionsCheck.CountryCity,
#RegionsCheck.Region,
#RegionsCheck.CountRegions
-- SELECT * FROM #RegionsCheckMostFrequent ORDER BY CountryCity ASC, CountRegions DESC
In the example concretely - the city 'Hamme' in Belgium has 7 times set '08', while 2 times '02', so would expect that in the MostFrequentSettings column the 1 should be beside of the '08' line.
But as you can see orange marked somehow that works opposite, could you please advise why and how it could be corrected?
Thanks in advance,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER