440,665 Members | 1,900 Online
Need help? Post your question and get tips & solutions from a community of 440,665 IT Pros & Developers. It's quick & easy.

# Best match query and order by columns

 P: 36 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: Expand|Select|Wrap|Line Numbers 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. Expand|Select|Wrap|Line Numbers     Pfx R3 R1 R4 R5 R2 12 2    5    5    5 8     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. Expand|Select|Wrap|Line Numbers   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 May 8 '08 #1
11 Replies

 Expert 2.5K+ P: 2,878 Option 1: There's always CURSOR. It could be slow, though. Option 2: This one will work in SQL 2005 and up ONLY. Expand|Select|Wrap|Line Numbers declare @Testpfx table ( 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(1235678,4,2,8,5,8) Insert into  @Testpfx values(123456789,1,3,9,2,6)   declare @Testpfx2 table (rowid int identity(1,1), pfx varchar(20), rColumns varchar(3), RValues money)   insert into @Testpfx2(pfx, rColumns, RValues) select TOP 100 percent pfx, RColumns, RValues from  (select pfx, RColumns, RValues from    (Select top 1 pfx, r1, r2, r3, r4, r5 from  @Testpfx    where patindex('%' + pfx + '%', '124654654') > 0    order by pfx desc) Rs UNPIVOT    (RValues for RColumns IN        (r1, r2, r3, r4, r5)    ) as vwUnPivot) vwSorted order by RValues     select pfx, [r1] as r1, [r2] as r2, [r3] as r3, [r4] as r4, [r5] as r5 from    (select pfx, 'r' + cast(rowid as varchar(15)) as newRColumn, Rvalues from @Testpfx2) vwSource PIVOT (    sum(RValues)    for newRColumn in ([r1], [r2], [r3],[r4],[r5]) ) as pvt order by pfx Some consideration: 1. The number of R's (the money) column is not that much that you can manually type this code. 2. The table size (# of records) is not that big. But if it's a big table, it might also be slow in any other technique you will use. 3. Consider using a temp table for @Testpfx2 so that you can create index. Happy Coding -- CK May 8 '08 #2

 Expert 100+ P: 1,134 Here is how I did it Since we are working with only 1 row once we have the closest match the humungus query won't be slow Expand|Select|Wrap|Line Numbers 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)       --Here I am creating a table variable to save the closest match into declare @tbl table(pfx varchar(20),R1 money,R2 money,R3 money,R4 money,R5 money)         --I used your method to get the row that matches closest --but I save it into a table variable  --so the humungus query only has to work with 1 row Declare @No varchar(20)  Set @No = '124654654'  insert into @tbl Select top 1 * from TestPfx Where @No like Pfx + '%' order by Pfx Desc         --And here is the humungus query to sort the columns Select 'PFX' as pfx     ,case when P1=4 then C1 else case when P2=4 then C2 else case when P3=4 then C3 else case when P4=4 then C4 else C5 end end end end as R1     ,case when P1=3 then C1 else case when P2=3 then C2 else case when P3=3 then C3 else case when P4=3 then C4 else C5 end end end end as R2     ,case when P1=2 then C1 else case when P2=2 then C2 else case when P3=2 then C3 else case when P4=2 then C4 else C5 end end end end as R3     ,case when P1=1 then C1 else case when P2=1 then C2 else case when P3=1 then C3 else case when P4=1 then C4 else C5 end end end end as R4     ,case when P1=0 then C1 else case when P2=0 then C2 else case when P3=0 then C3 else case when P4=0 then C4 else C5 end end end end as R5 from ( select pfx,R1, case when R1

 P: 36 Hi Delerna, WOW! and I really mean it. Thank you so much for helping me with this humungous query. It actually turned out to be much bigger than I thought it would be but the result is optimal. I am unable to find words to help you as in my vain attempts I was literally going round n round and somewhere or, the other something got missed out. Now, I'll try and get this BIG query to be somewhat dynamic as the number of columns in my table keeps changing according to requirements. Sometimes they could be 3 and sometimes 6 and another time 4. Thanks once again. I really appreciate yourself taking time to help me out. Ankit Mathur Here is how I did it Since we are working with only 1 row once we have the closest match the humungus query won't be slow Expand|Select|Wrap|Line Numbers 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)       --Here I am creating a table variable to save the closest match into declare @tbl table(pfx varchar(20),R1 money,R2 money,R3 money,R4 money,R5 money)         --I used your method to get the row that matches closest --but I save it into a table variable  --so the humungus query only has to work with 1 row Declare @No varchar(20)  Set @No = '124654654'  insert into @tbl Select top 1 * from TestPfx Where @No like Pfx + '%' order by Pfx Desc         --And here is the humungus query to sort the columns Select 'PFX' as pfx ,case when P1=4 then C1 else case when P2=4 then C2 else case when P3=4 then C3 else case when P4=4 then C4 else C5 end end end end as R1 ,case when P1=3 then C1 else case when P2=3 then C2 else case when P3=3 then C3 else case when P4=3 then C4 else C5 end end end end as R2 ,case when P1=2 then C1 else case when P2=2 then C2 else case when P3=2 then C3 else case when P4=2 then C4 else C5 end end end end as R3 ,case when P1=1 then C1 else case when P2=1 then C2 else case when P3=1 then C3 else case when P4=1 then C4 else C5 end end end end as R4 ,case when P1=0 then C1 else case when P2=0 then C2 else case when P3=0 then C3 else case when P4=0 then C4 else C5 end end end end as R5 from ( select pfx,R1, case when R1

 P: 36 Hi CK, Thanks for your input and I appreciate your help. As of now, my current requirement is for SQL 2000. But we're about to migrate to SQL 2005. So am sure your code would be of great help for me to determine the best way possible in near future. Just to answer some of your points. 1. Number of columns (R's) keep varying. 2. Number of rows will always be more than 15000+ and they too keep varying. 3. In my attempts too I was trying for swapping in temp table. But I was trying to sort only the values I recd. from my query in temp table. I thought that would be enough. Thanks again for helping me out. Ankit Mathur Option 1: There's always CURSOR. It could be slow, though. Option 2: This one will work in SQL 2005 and up ONLY. Expand|Select|Wrap|Line Numbers declare @Testpfx table ( 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(1235678,4,2,8,5,8) Insert into @Testpfx values(123456789,1,3,9,2,6)   declare @Testpfx2 table (rowid int identity(1,1), pfx varchar(20), rColumns varchar(3), RValues money)   insert into @Testpfx2(pfx, rColumns, RValues) select TOP 100 percent pfx, RColumns, RValues from  (select pfx, RColumns, RValues from (Select top 1 pfx, r1, r2, r3, r4, r5 from @Testpfx where patindex('%' + pfx + '%', '124654654') > 0 order by pfx desc) Rs UNPIVOT (RValues for RColumns IN  (r1, r2, r3, r4, r5) ) as vwUnPivot) vwSorted order by RValues     select pfx, [r1] as r1, [r2] as r2, [r3] as r3, [r4] as r4, [r5] as r5 from (select pfx, 'r' + cast(rowid as varchar(15)) as newRColumn, Rvalues from @Testpfx2) vwSource PIVOT ( sum(RValues) for newRColumn in ([r1], [r2], [r3],[r4],[r5]) ) as pvt order by pfx Some consideration: 1. The number of R's (the money) column is not that much that you can manually type this code. 2. The table size (# of records) is not that big. But if it's a big table, it might also be slow in any other technique you will use. 3. Consider using a temp table for @Testpfx2 so that you can create index. Happy Coding -- CK May 9 '08 #5

 P: 36 Hi Delerna, WOW! and I really mean it. Thank you so much for helping me with this humungous query. It actually turned out to be much bigger than I thought it would be but the result is optimal. I am unable to find words to help you as in my vain attempts I was literally going round n round and somewhere or, the other something got missed out. Now, I'll try and get this BIG query to be somewhat dynamic as the number of columns in my table keeps changing according to requirements. Sometimes they could be 3 and sometimes 6 and another time 4. Thanks once again. I really appreciate yourself taking time to help me out. Ankit Mathur May 9 '08 #6

 P: 36 Hi Delerna, Can you please explain what exactly P1, P2, P3, P4, P5 and C1, C2, C3, C4, C5 have been created for. In my requirement I just want the values to come in order. No need for their respective columns also to come as values. So I thought if you could explain me their purpose maybe I can curtail your query to display only the values in order without significantly affecting the code. Would look forward to your reply. Ankit Here is how I did it Since we are working with only 1 row once we have the closest match the humungus query won't be slow Expand|Select|Wrap|Line Numbers 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)       --Here I am creating a table variable to save the closest match into declare @tbl table(pfx varchar(20),R1 money,R2 money,R3 money,R4 money,R5 money)         --I used your method to get the row that matches closest --but I save it into a table variable  --so the humungus query only has to work with 1 row Declare @No varchar(20)  Set @No = '124654654'  insert into @tbl Select top 1 * from TestPfx Where @No like Pfx + '%' order by Pfx Desc         --And here is the humungus query to sort the columns Select 'PFX' as pfx ,case when P1=4 then C1 else case when P2=4 then C2 else case when P3=4 then C3 else case when P4=4 then C4 else C5 end end end end as R1 ,case when P1=3 then C1 else case when P2=3 then C2 else case when P3=3 then C3 else case when P4=3 then C4 else C5 end end end end as R2 ,case when P1=2 then C1 else case when P2=2 then C2 else case when P3=2 then C3 else case when P4=2 then C4 else C5 end end end end as R3 ,case when P1=1 then C1 else case when P2=1 then C2 else case when P3=1 then C3 else case when P4=1 then C4 else C5 end end end end as R4 ,case when P1=0 then C1 else case when P2=0 then C2 else case when P3=0 then C3 else case when P4=0 then C4 else C5 end end end end as R5 from ( select pfx,R1, case when R1

 P: 36 Hi Delerna, Can you please explain what exactly P1, P2, P3, P4, P5 and C1, C2, C3, C4, C5 have been created for. In my requirement I just want the values to come in order. No need for their respective columns also to come as values. So I thought if you could explain me their purpose maybe I can curtail your query to display only the values in order without significantly affecting the code. Would look forward to your reply. Ankit May 9 '08 #8

 Expert 100+ P: 1,134 You need to sort the R columns from lowest value to highest vale going from left to right. There is no way to sort columns so.... What I did was to count the number of columns that had a value that was less than the value in each particular column. So, for each column in turn, I check its value agaist each of the other columns and return 1 if the other column is higher and 0 if its not. The results of each check is then added together to get the position of the row in the sort order. That count is in P1,P2,P3,P4,P5. and P means "Position" of the column in sort order So, since there were 5 columns in your example if an R# column is the lowest value then its related P# column will be 4 if an R# column is the second lowest value then its related P# column will be 3 ... if an R# column is the highest value then P# will be 0 There is an extra problem here to complicate things (arent there always?) That problem is that multiple columns can have the same value. so here I did a similar thing. I check each of the columns prior to that colum to see if the values are the same. 1 if they are and 0 if they are not. The results are added to the previous results to get the true position like this Expand|Select|Wrap|Line Numbers ...R1..R2..R3..R4..R5    4...2...9...1....4    1...3..0....4....1     count of values higher    0...0..0....0....1     count of previous columns that are the same P  1...3...0...4....2   I hope all of that makes sense. It was hard to explain. C# is just used to hold the column name and since you don't care we can drop all of that and the humungous query will become just big :) May 10 '08 #9

 Expert 100+ P: 1,134 Here it is Expand|Select|Wrap|Line Numbers Select pfx,case when P1=4 then R1 else case when P2=4 then R2 else case when P3=4 then R3 else case when P4=4 then R4 else R5 end end end end as R1 ,case when P1=3 then R1 else case when P2=3 then R2 else case when P3=3 then R3 else case when P4=3 then R4 else R5 end end end end as R2 ,case when P1=2 then R1 else case when P2=2 then R2 else case when P3=2 then R3 else case when P4=2 then R4 else R5 end end end end as R3 ,case when P1=1 then R1 else case when P2=1 then R2 else case when P3=1 then R3 else case when P4=1 then R4 else R5 end end end end as R4 ,case when P1=0 then R1 else case when P2=0 then R2 else case when P3=0 then R3 else case when P4=0 then R4 else R5 end end end end as R5 from ( select pfx,R1, case when R1

 P: 36 Whoops! Your reply actually made me think, I asked a little too much from you. :) I must say. Its a very well explained answer. Though it took a couple of readings to start understanding it (and am still so very sure that am gonna read it more than once). But it was necessary to understand the logic. Atleast now I can think of playing with your code. Your code is a beauty to me and untill now it remained untouched. I really appreciate yourself taking time out first for giving me that BIG query and then for explaining it. Thanks again for all the help. Ankit Mathur May 10 '08 #11

 Expert 100+ P: 1,134 You are welcome. Thats why I come here. sometimes to get help for myself and sometimes to give help to others. And nothing beats being able to assist someone else. In my opinion! May 11 '08 #12