Link to home
Start Free TrialLog in
Avatar of csehz
csehzFlag for Hungary

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.

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

Open in new window


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.

User generated image
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
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of csehz

ASKER

Thanks for the answer