Best match query and order by columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best match query and order by columns

Author

Topic

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-08 : 05:51:27

Hi,

I'm trying to overcome a situation whereby I have to search through 4-5 columns and produce the results with an order by according to the values matched in these columns.

Example:

My Table Structure:

Create Table TestPfx
(
pfx varchar(20),
R1 money,
R2 money,
R3 money,
R4 money,
R5 money,
)
Insert into Testpfx values(1,1,7,1,3,9)
Insert into Testpfx values(12,5,8,2,5,5)
Insert into Testpfx values(123,8,9,3,7,1)
Insert into Testpfx values(1234,3,4,4,1,7)
Insert into Testpfx values(12345,6,5,5,5,5)
Insert into Testpfx values(123456,9,6,6,9,3)
Insert into Testpfx values(1234567,7,1,7,8,4)
Insert into Testpfx values(12345678,4,2,8,5,8)
Insert into Testpfx values(123456789,1,3,9,2,6)
Select * from Testpfx

Now, If I enter a value 124654654. I need this value to be best matched amongst those in my table. Like here value 12 will be best matched as we don't have a pfx value 124 in our table. had it been there it should've been my best match unless 1246 is present in pfx.

I hope I'm clear with this point.

Secondly, I need my output to be in an Ascending Order according to the column values for the selected best matched row. Like in our example, our best matched row is 12. So my output should look like this.

Pfx R3 R1 R4 R5 R2
12 2 5 5 5 8

If the searched number is 1230022827 result should be
Pfx R3 R1 R4 R5 R2
123 1 3 7 8 9

As part of my efforts so far I believe this should act as a query to BEST MATCH and find that single row. But I'm not too sure if its the best way.

Declare @No varchar(20)
Set @No = '124654654'
Select top 1 * from Rates Where @No like Pfx + '%' order by Pfx Desc

Please help as I'm totally getting clueless with arranging the columns in the best order.

I hope I'm clear with my problem.

Will look forward to the reply.

Ankit Mathur

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 06:13:26

[code]DECLARE @TestPfx TABLE (pfx VARCHAR(20), r1 MONEY, r2 MONEY, r3 MONEY, r4 MONEY, r5 MONEY)

INSERT @TestPfx
SELECT '1', 1, 7, 1, 3, 9 UNION ALL
SELECT '12', 5, 8, 2, 5, 5 UNION ALL
SELECT '123', 8, 9, 3, 7, 1 UNION ALL
SELECT '1234', 3, 4, 4, 1, 7 UNION ALL
SELECT '12345', 6, 5, 5, 5, 5 UNION ALL
SELECT '123456', 9, 6, 6, 9, 3 UNION ALL
SELECT '1234567', 7, 1, 7, 8, 4 UNION ALL
SELECT '12345678', 4, 2, 8, 5, 8 UNION ALL
SELECT '123456789', 1, 3, 9, 2, 6

DECLARE @Search VARCHAR(20)

SET @Search = '124654654'

SELECT TOP 1 t.*
FROM @TestPfx AS t
WHERE @Search LIKE t.pfx + '%'
ORDER BY LEN(t.pfx) DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-08 : 08:50:23

Hi Peso,

Thanks for replying. But your solution didn't worked.

It still returned the same ordering as its in Testpfx.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-08 : 10:45:25

Perhaps you need to use the Levenstein Edit Distance Algorithm

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51540&whichpage=1

An infinite universe is the ultimate cartesian product.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 11:17:13

With your test data above, which record will search data "12305121311" return?

pfx = 123 or pfx = 12345 ?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 11:23:46

quote:
Originally posted by Ankit Mathur

Thanks for replying. But your solution didn't worked.

For the sample data posted and example search values it does work.
You will have to post better sample data or another search values to illustrate why you think it does not work.

E 12°55'05.25"
N 56°04'39.16"

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-09 : 01:43:52

Hi Peso,

For "12305121311" pfx 123 should be returned as we don't have 1230 in our table. So the best match would be 123.


This is how my table is looking. 

Create table syntax and Insert queries already given above.

Select * from Testpfx

pfx	        R1	R2	R3	R4	R5
1	        1.0000	7.0000	1.0000	3.0000	9.0000
12	        5.0000	8.0000	2.0000	5.0000	5.0000
123	        8.0000	9.0000	3.0000	7.0000	1.0000
1234	        3.0000	4.0000	4.0000	1.0000	7.0000
12345	        6.0000	5.0000	5.0000	5.0000	5.0000
123456	        9.0000	6.0000	6.0000	9.0000	3.0000
1234567	        7.0000	1.0000	7.0000	8.0000	4.0000
12345678	4.0000	2.0000	8.0000	5.0000	8.0000
123456789	1.0000	3.0000	9.0000	2.0000	6.0000

And this is how I'm getting a result after the following query.


Declare @Search varchar(20) 
Set @Search = '1230022827' 
SELECT TOP 1	t.*
FROM		TestPfx AS t
WHERE		@Search LIKE t.pfx + '%'
ORDER BY	LEN(t.pfx) DESC

pfx	R1	R2	R3	R4	R5
123	8.0000	9.0000	3.0000	7.0000	1.0000

While the desired output is as follows


pfx	R5	R3	R4	R1	R2
123	1.0000	3.0000	7.0000	8.0000	9.0000

I've retried your query several times with different values but its reflecting the same way. It's not ordering the columns in an ascending order.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 03:41:47

You really want to reorder the column output to reflect the numeric order of values.
And that the column names to changed accordingly?

Phew...

E 12°55'05.25"
N 56°04'39.16"

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-09 : 03:59:49

I only want re-arrangement of column values.

No need for changing column names.

I'm sorry. I guess I couldn't clear how I want my data to be.

pfx R1 R2 R3 R4 R5
123 8.0000 9.0000 3.0000 7.0000 1.0000

While the desired output is as follows

pfx R1 R2 R3 R4 R5
123 1.0000 3.0000 7.0000 8.0000 9.0000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 04:06:39

[code]DECLARE @Search VARCHAR(20)

SET @Search = '1230022827'

SELECT TOP 1 t.*
INTO #Temp
FROM TestPfx AS t
WHERE @Search LIKE t.pfx + '%'
ORDER BY LEN(t.pfx) DESC

DECLARE @Temp TABLE (RowID INT IDENTITY(1, 1), pfx VARCHAR(20), Value MONEY)

INSERT @Temp
SELECT Value
FROM (
SELECT pfx,
R1 AS Value
FROM #Temp

UNION ALL

SELECT pfx,
R2
FROM #Temp

SELECT pfx,
R3
FROM #Temp

SELECT pfx,
R4
FROM #Temp

SELECT pfx,
R5
FROM #Temp
) AS d
ORDER BY Value

SELECT pfx,
MAX(CASE WHEN RowID = 1 THEN Value ELSE '' END) AS R1,
MAX(CASE WHEN RowID = 2 THEN Value ELSE '' END) AS R2,
MAX(CASE WHEN RowID = 3 THEN Value ELSE '' END) AS R3,
MAX(CASE WHEN RowID = 4 THEN Value ELSE '' END) AS R4,
MAX(CASE WHEN RowID = 5 THEN Value ELSE '' END) AS R5
FROM @Temp
GROUP BY pfx

DROP TABLE #Temp[/code]

E 12°55'05.25"
N 56°04'39.16"

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-10 : 01:43:18

Hi Peso,

Thanks for giving your time and helping me out to solve this problem.

The code you provided in yuor last post is giving some errors.

Can you please explain the logic of your code and paste out the resultset you are getting after executing your code?

I think that would be of great help.

Thanks Again
Ankit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 01:50:34

quote:

Originally posted by Peso

DECLARE	@Search VARCHAR(20)

SET	@Search = '1230022827'

SELECT TOP 1	t.*
INTO		#Temp
FROM		TestPfx AS t
WHERE		@Search LIKE t.pfx + '%'
ORDER BY	LEN(t.pfx) DESC

DECLARE	@Temp TABLE (RowID INT IDENTITY(1, 1), pfx VARCHAR(20), Value MONEY)

INSERT		@Temp (pfx, Value)
SELECT		pfx,Value
FROM		(
			SELECT	pfx,
				R1 AS Value
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R2
			FROM	#Temp

			SELECT	pfx,
				R3
			FROM	#Temp

			SELECT	pfx,
				R4
			FROM	#Temp

			SELECT	pfx,
				R5
			FROM	#Temp
		) AS d
ORDER BY	Value

SELECT		pfx,
		MAX(CASE WHEN RowID = 1 THEN Value ELSE '' END) AS R1,
		MAX(CASE WHEN RowID = 2 THEN Value ELSE '' END) AS R2,
		MAX(CASE WHEN RowID = 3 THEN Value ELSE '' END) AS R3,
		MAX(CASE WHEN RowID = 4 THEN Value ELSE '' END) AS R4,
		MAX(CASE WHEN RowID = 5 THEN Value ELSE '' END) AS R5
FROM		@Temp
GROUP BY	pfx

DROP TABLE	#Temp

E 12°55'05.25"
N 56°04'39.16"

Small modifications done. Try this.

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-10 : 02:19:47

Hi Visakh,

Thanks for chipping in with your inputs too.

i'm getting these errors.

Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 37
Line 37: Incorrect syntax near ')'.

Can you explain why are these cropping up. My mind has totally screwed up with this problem and am unable to understand certain simple things too.

Thanks
Ankit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 02:35:32

quote:
Originally posted by Ankit Mathur

Hi Visakh,

Thanks for chipping in with your inputs too.

i'm getting these errors.

Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 37
Line 37: Incorrect syntax near ')'.

Can you explain why are these cropping up. My mind has totally screwed up with this problem and am unable to understand certain simple things too.

Thanks
Ankit

Hi Ankit
Can you try like this?

DECLARE	@Search VARCHAR(20)

SET	@Search = '1230022827'

SELECT TOP 1	t.*
INTO		#Temp
FROM		TestPfx AS t
WHERE		@Search LIKE t.pfx + '%'
ORDER BY	LEN(t.pfx) DESC

DECLARE	@Temp TABLE (RowID INT IDENTITY(1, 1), pfx VARCHAR(20), Value MONEY)

INSERT		@Temp (pfx, Value)
SELECT		pfx,Value
FROM		(
			SELECT	pfx,
				R1 AS Value
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R2
			FROM	#Temp

			SELECT	pfx,
				R3
			FROM	#Temp

			SELECT	pfx,
				R4
			FROM	#Temp

			SELECT	pfx,
				R5
			FROM	#Temp
		) AS d
ORDER BY	Value

SELECT		pfx,
		MAX(CASE WHEN RowID = 1 THEN Value ELSE NULL END) AS R1,
		MAX(CASE WHEN RowID = 2 THEN Value ELSE NULL END) AS R2,
		MAX(CASE WHEN RowID = 3 THEN Value ELSE NULL END) AS R3,
		MAX(CASE WHEN RowID = 4 THEN Value ELSE NULL END) AS R4,
		MAX(CASE WHEN RowID = 5 THEN Value ELSE NULL END) AS R5
FROM		@Temp
GROUP BY	pfx

DROP TABLE	#Temp

If it still errors,can you confirm you are using the same query without adding anything else? In any case can you post your full query used as i cant spot any obvious errors in above query.

Ankit Mathur
Starting Member

24 Posts

Posted - 2008-05-10 : 14:30:59

Hi Vishakh,

Finally it worked. There were UNION ALL also missing. I tried after putting it in earlier too but then was getting an implicit conversion error.

The final code looks like this.



DECLARE	@Search VARCHAR(20)

SET	@Search = '1230022827'

SELECT TOP 1	t.*
INTO		#Temp
FROM		TestPfx AS t
WHERE		@Search LIKE t.pfx + '%'
ORDER BY	LEN(t.pfx) DESC

DECLARE	@Temp TABLE (RowID INT IDENTITY(1, 1), pfx VARCHAR(20), Value MONEY)

INSERT		@Temp (pfx, Value)
SELECT		pfx,Value
FROM		(
			SELECT	pfx,
				R1 AS Value
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R2
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R3
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R4
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R5
			FROM	#Temp
		) AS d
ORDER BY	Value

SELECT		pfx,
		MAX(CASE WHEN RowID = 1 THEN Value ELSE NULL END) AS R1,
		MAX(CASE WHEN RowID = 2 THEN Value ELSE NULL END) AS R2,
		MAX(CASE WHEN RowID = 3 THEN Value ELSE NULL END) AS R3,
		MAX(CASE WHEN RowID = 4 THEN Value ELSE NULL END) AS R4,
		MAX(CASE WHEN RowID = 5 THEN Value ELSE NULL END) AS R5
FROM		@Temp
GROUP BY	pfx

DROP TABLE	#Temp

I'd like to thank both you and Peso who was very helpful indeed. Without you guys it would've been a tough task to get through.

With this I may term this post as SOLVED.

Thanks again.

Ankit Mathur

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 14:40:15

quote:

Originally posted by Ankit Mathur

Hi Vishakh,

Finally it worked. There were UNION ALL also missing. I tried after putting it in earlier too but then was getting an implicit conversion error.

The final code looks like this.



DECLARE	@Search VARCHAR(20)

SET	@Search = '1230022827'

SELECT TOP 1	t.*
INTO		#Temp
FROM		TestPfx AS t
WHERE		@Search LIKE t.pfx + '%'
ORDER BY	LEN(t.pfx) DESC

DECLARE	@Temp TABLE (RowID INT IDENTITY(1, 1), pfx VARCHAR(20), Value MONEY)

INSERT		@Temp (pfx, Value)
SELECT		pfx,Value
FROM		(
			SELECT	pfx,
				R1 AS Value
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R2
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R3
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R4
			FROM	#Temp

			UNION ALL

			SELECT	pfx,
				R5
			FROM	#Temp
		) AS d
ORDER BY	Value

SELECT		pfx,
		MAX(CASE WHEN RowID = 1 THEN Value ELSE NULL END) AS R1,
		MAX(CASE WHEN RowID = 2 THEN Value ELSE NULL END) AS R2,
		MAX(CASE WHEN RowID = 3 THEN Value ELSE NULL END) AS R3,
		MAX(CASE WHEN RowID = 4 THEN Value ELSE NULL END) AS R4,
		MAX(CASE WHEN RowID = 5 THEN Value ELSE NULL END) AS R5
FROM		@Temp
GROUP BY	pfx

DROP TABLE	#Temp

I'd like to thank both you and Peso who was very helpful indeed. Without you guys it would've been a tough task to get through.

With this I may term this post as SOLVED.

Thanks again.

Ankit Mathur

Ah that was cool. DIdnt spot that one

. You're welcome Ankit.Glad that we could help you on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources