SQL - Formula to count the top 3 usage

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

*/

Open in new window

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,

SQLMicrosoft SQL Server

Last Comment

csehz

8/22/2022 - Mon

slightwv (䄆 Netminder)

6/8/2021

Your SQL Server version would help a lot.

As long as FieldUsage is ALWAYS in the order you want, what you are really after is to 'trim' everything after the third '/'.

This works on the data provided. It uses a recursive CTE. As long as there isn't a LOT of rows, it should be fine. They don't scale well.

with recursive_cte as (
    select 1 as row, FieldType, charindex('/', FieldUsage) offset, FieldUsage + '/' FieldUsage
    from #TempTable
    union all
    select row + 1, FieldType, charindex('/', FieldUsage, offset + 1), FieldUsage
    from recursive_cte
    where offset>0 and row<=2
),
get_correct_row as (
	select row, FieldType, FieldUsage, offset, row_number() over(partition by FieldType order by offset desc) rn
	from recursive_cte 
)
select 
FieldType, FieldUsage, SubString(FieldUsage,1,offset-1) Top3Countries
from get_correct_row
where rn=1

Open in new window

My test Fiddle is here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7e429c26d7c97497a9e736c9ac3391b1

PortletPaul

6/8/2021

CHARINDEX() will return the position of a string within a string. Using a combination of those will locate the third position of '/' and then LEFT() can be used to output that portion of the source string.

I have used an OUTER APPLY just in case there are missing strings and a case expression if there isn't a value returned by the apply.

select
     FieldType
   , FieldUsage
   , case when oa.pos > 0 then left(FieldUsage,oa.pos-1) end as Top3Countries
from #TempTable
outer apply (select charindex('/',FieldUsage,charindex('/',FieldUsage,charindex('/',FieldUsage,1)+1)+1) as pos
                    ) oa

Open in new window

SOLUTION

slightwv (䄆 Netminder)

6/8/2021

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.