SQLTeam.com | Weblogs | Forums

Join based on the maximum letter match from other Table

#1

</ Hi,I have a scenario where we have to match the record based on the highest value of sequence matched in 2nd table.
In the 1st table I have 5 rows and in 2nd table I have corresponding matching records .for an example in 1st table I have a record with Value 123456789 but in 2nd table I can have values like ,
1
12
123
1234
12345
So in this case the last value which is matching the most letters should match and get the result output...The output expected:
123456789 -> 12345

Below is some example and expected output required:

TABLE_1
COL_1

123456789
195678434
114678900
456789800
112332456

TABLE_2
COL_1 NAME
1 A
12 B
123 C
1234 D
12345 E <- This should match because max character is matching in a sequence
1956 A
195678 B <- This should match because max character is matching in a sequence
1146 A
11467 C <- This should match because max character is matching in a sequence
4567 A <- This should match because max character is matching in a sequence
112332456 E <- This should match because max character is matching in a sequence
EXPECTED OUTPUT
COL_1 NAME
123456789 E
195678434 B
114678900 C
456789800 A
112332456 E
#2

hi

i am having difficulty understanding what you are saying

#3

By now you should know to post comsumable test data!
I am assuming the number are strings, if not you will have to do the casting yourself.

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t1
(
	Col1 varchar(10) NOT NULL PRIMARY KEY
);
INSERT INTO #t1
VALUES ('123456789'), ('195678434')
	,('114678900'), ('456789800'), ('112332456');

CREATE TABLE #t2
(
	Col1 varchar(10) NOT NULL PRIMARY KEY
	,ColName char(1) NOT NULL
);
INSERT INTO #t2
VALUES ('1', 'A'), ('12', 'B'), ('123', 'C'), ('1234', 'D'), ('12345', 'E'), ('1956', 'A')
	,('195678', 'B'), ('1146', 'A'), ('11467', 'C'), ('4567', 'A'), ('112332456', 'E');

The following seems to do what you want:

WITH Col1Order
AS
(
	SELECT T1.Col1, T2.ColName
		,ROW_NUMBER() OVER (PARTITION BY T1.Col1 ORDER BY T2.ColName DESC) AS rn
	FROM #t1 T1
		JOIN #t2 T2
			ON T1.Col1 LIKE T2.Col1 + '%'
)
SELECT Col1, ColName
FROM Col1Order
WHERE rn = 1;
1 Like
#4

An alternative method, that will also check for no matching row at all:


SELECT *
FROM #t1 t1
OUTER APPLY (
    SELECT TOP (1) *
    FROM #t2 t2
    WHERE t1.Col1 LIKE t2.Col1 + '%'
    ORDER BY LEN(t2.Col1) DESC
) AS t2
#5

Hi Team, Thanks for this. Few concerns.

  1. Is there any other way to get rid of Row_Number because both the tables are having 50Million data and it will take a lot of time. Is there anyway we can introduce regular expression to match the highest matching string directly.
  2. On this PARTITION BY T1.Col1 ORDER BY T2.ColName DESC , it may not be correct always that we have (ORDER BY T2.ColName DESC ) T2.colname id desc order .May have few will be in ascending , only thing we have to pull the respective column from that user
#6

I already provided an alternate method. For performance, you should create an index to support the lookup in the OUTER APPLY query.

#7

Well, your idea is good. We are migrating from SQL server to Snowflake. So looking for some equivalent. Row Number is good but takes lot of time

#8

Is it possible for you to convert with Joins?