• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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.

Printscreen about the result of the query
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,
0
csehz
Asked:
csehz
1 Solution
 
Mark WillsTopic AdvisorCommented:
Well, count in that setting doesnt work - it ends up a progressive count

you are better off select from a derived table (or CTE) to get your counts worked out first...

SELECT
	ROW_NUMBER() OVER (PARTITION BY a.CountryCity ORDER BY CountedRegions DESC) AS MostFrequentSettings,
	a.Country,
	a.City,
	a.CountryCity,
	a.Region,
	a.CountRegions
INTO
	#RegionsCheckMostFrequent
FROM

(SELECT COUNT(*) as CountedRegions,
	#RegionsCheck.Country,
	#RegionsCheck.City,
	#RegionsCheck.CountryCity,
	#RegionsCheck.Region,
	#RegionsCheck.CountRegions
FROM
	#RegionsCheck
GROUP BY
	#RegionsCheck.Country,
	#RegionsCheck.City,
	#RegionsCheck.CountryCity,
	#RegionsCheck.Region,
	#RegionsCheck.CountRegions) a

Open in new window

or there abouts...
0
 
csehzIT consultantAuthor Commented:
Thanks for the answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now