troubleshooting Question

SQL - Formula to count the top 3 usage

Avatar of csehz
csehzFlag for Hungary asked on
SQLMicrosoft SQL Server
5 Comments2 Solutions20 ViewsLast Modified:
Dear Experts,

Having a complex query result and searching for a formula in SQL, which could determine the top 3 countries in usage.

If you run the following code (Microsoft SQL Server) that setups the example,

CREATE TABLE
   #TempTable (
      FieldType nvarchar(20),
      FieldUsage nvarchar(500)
      );

INSERT INTO
   #TempTable (FieldType, FieldUsage)
VALUES
   ('Field1', 'DE Germany 18799 /US USA 11913 /BR Brazil 9840 /CN China 9436 /IN India 9113 /GB United Kingdom 8910'),
   ('Field2', 'CN China 446 /DE Germany 102'),
   ('Field3', 'PL Poland 91 /US USA 44 /FR France 43 /IN India 22 /AR Argentina 17 /SE Sweden 16')

SELECT 
   * 
FROM 
   #TempTable

/*

DELETE FROM
   #TempTable

DROP TABLE
   #TempTable

*/

which is so in column FieldUsage a pattern of Country code + Country name + CountOfFields used with separating '/' character (if you look on the numbers they are decreasing, so the top usage is the first country, then comes the second etc.):



The target would be an SQL formula which results as it is shown on the below picture in column C, briefly cutting the LEFT three countries from column B (if there are just two countries, then two) to determine the top 3 usage:



Could you please advise which SQL formula could result that?

In the real environment the FieldUsage field can be very long, sometimes listing 30-40 countries (meaning that 30-40 times being the '/' separator there)

Thanks in advance,
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros