SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best match query and order by columns


Best match query and order by columns

Author
Message
Ankit Mathur-481681
Ankit Mathur-481681
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 360
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search