By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,665 Members | 1,900 Online
Bytes IT Community
+ Ask a Question
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
  1. My Table Structure:
  2.  
  3.  
  4. Create Table TestPfx
  5. (
  6. pfx varchar(20),
  7. R1 money,
  8. R2 money,
  9. R3 money,
  10. R4 money,
  11. R5 money,
  12. )
  13. Insert into Testpfx values(1,1,7,1,3,9)
  14. Insert into Testpfx values(12,5,8,2,5,5)
  15. Insert into Testpfx values(123,8,9,3,7,1)
  16. Insert into Testpfx values(1234,3,4,4,1,7)
  17. Insert into Testpfx values(12345,6,5,5,5,5)
  18. Insert into Testpfx values(123456,9,6,6,9,3)
  19. Insert into Testpfx values(1234567,7,1,7,8,4)
  20. Insert into Testpfx values(12345678,4,2,8,5,8)
  21. Insert into Testpfx values(123456789,1,3,9,2,6)
  22. Select * from Testpfx
  23.  
  24.  
  25.  
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
  1.  
  2.  
  3. Pfx R3 R1 R4 R5 R2
  4. 12 2    5    5    5 8
  5.  
  6.  
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
  1.  
  2. Declare @No varchar(20) 
  3. Set @No = '124654654' 
  4. Select top 1 * from Rates Where @No like Pfx + '%' order by Pfx Desc
  5.  
  6.  
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
Share this Question
Share on Google+
11 Replies


ck9663
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
  1. declare @Testpfx table
  2. (
  3. pfx varchar(20),
  4. R1 money,
  5. R2 money,
  6. R3 money,
  7. R4 money,
  8. R5 money
  9. )
  10. Insert into  @Testpfx values(1,1,7,1,3,9)
  11. Insert into  @Testpfx values(12,5,8,2,5,5)
  12. Insert into  @Testpfx values(123,8,9,3,7,1)
  13. Insert into  @Testpfx values(1234,3,4,4,1,7)
  14. Insert into  @Testpfx values(12345,6,5,5,5,5)
  15. Insert into  @Testpfx values(123456,9,6,6,9,3)
  16. Insert into  @Testpfx values(1234567,7,1,7,8,4)
  17. Insert into  @Testpfx values(1235678,4,2,8,5,8)
  18. Insert into  @Testpfx values(123456789,1,3,9,2,6)
  19.  
  20. declare @Testpfx2 table (rowid int identity(1,1), pfx varchar(20), rColumns varchar(3), RValues money)
  21.  
  22. insert into @Testpfx2(pfx, rColumns, RValues)
  23. select TOP 100 percent pfx, RColumns, RValues
  24. from 
  25. (select pfx, RColumns, RValues
  26. from
  27.    (Select top 1 pfx, r1, r2, r3, r4, r5 from  @Testpfx
  28.    where patindex('%' + pfx + '%', '124654654') > 0
  29.    order by pfx desc) Rs
  30. UNPIVOT
  31.    (RValues for RColumns IN 
  32.       (r1, r2, r3, r4, r5)
  33.    ) as vwUnPivot) vwSorted order by RValues
  34.  
  35.  
  36. select pfx, [r1] as r1, [r2] as r2, [r3] as r3, [r4] as r4, [r5] as r5
  37. from
  38.    (select pfx, 'r' + cast(rowid as varchar(15)) as newRColumn, Rvalues from @Testpfx2) vwSource
  39. PIVOT
  40. (
  41.    sum(RValues)
  42.    for newRColumn in ([r1], [r2], [r3],[r4],[r5])
  43. ) 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

Delerna
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
  1. Create Table TestPfx
  2. (
  3. pfx varchar(20),
  4. R1 money,
  5. R2 money,
  6. R3 money,
  7. R4 money,
  8. R5 money,
  9. )
  10. Insert into Testpfx values(1,1,7,1,3,9)
  11. Insert into Testpfx values(12,5,8,2,5,5)
  12. Insert into Testpfx values(123,8,9,3,7,1)
  13. Insert into Testpfx values(1234,3,4,4,1,7)
  14. Insert into Testpfx values(12345,6,5,5,5,5)
  15. Insert into Testpfx values(123456,9,6,6,9,3)
  16. Insert into Testpfx values(1234567,7,1,7,8,4)
  17. Insert into Testpfx values(12345678,4,2,8,5,8)
  18. Insert into Testpfx values(123456789,1,3,9,2,6)
  19.  
  20.  
  21.  
  22. --Here I am creating a table variable to save the closest match into
  23. declare @tbl table(pfx varchar(20),R1 money,R2 money,R3 money,R4 money,R5 money)
  24.  
  25.  
  26.  
  27.  
  28. --I used your method to get the row that matches closest
  29. --but I save it into a table variable 
  30. --so the humungus query only has to work with 1 row
  31. Declare @No varchar(20) 
  32. Set @No = '124654654' 
  33. insert into @tbl
  34. Select top 1 * from TestPfx Where @No like Pfx + '%' order by Pfx Desc
  35.  
  36.  
  37.  
  38.  
  39. --And here is the humungus query to sort the columns
  40. Select 'PFX' as pfx
  41.     ,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
  42.     ,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
  43.     ,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
  44.     ,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
  45.     ,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
  46. from
  47. (
  48. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1,'R1' as C1
  49.     ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2,'R2' as C2
  50.     ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3,'R3' as C3
  51.     ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4,'R4' as C4
  52.     ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5,'R5' as C5 
  53. from @tbl
  54. )a
  55. union all
  56. Select CONVERT(VARCHAR(10),pfx)
  57.     ,CONVERT(VARCHAR(10),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
  58.     ,CONVERT(VARCHAR(10),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
  59.     ,CONVERT(VARCHAR(10),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
  60.     ,CONVERT(VARCHAR(10),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
  61.     ,CONVERT(VARCHAR(10),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
  62. from
  63. (
  64. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1,'R1' as C1
  65.     ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2,'R2' as C2
  66.     ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3,'R3' as C3
  67.     ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4,'R4' as C4
  68.     ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5,'R5' as C5 
  69. from @tbl
  70. )a
  71.  
May 9 '08 #3

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
  1. Create Table TestPfx
  2. (
  3. pfx varchar(20),
  4. R1 money,
  5. R2 money,
  6. R3 money,
  7. R4 money,
  8. R5 money,
  9. )
  10. Insert into Testpfx values(1,1,7,1,3,9)
  11. Insert into Testpfx values(12,5,8,2,5,5)
  12. Insert into Testpfx values(123,8,9,3,7,1)
  13. Insert into Testpfx values(1234,3,4,4,1,7)
  14. Insert into Testpfx values(12345,6,5,5,5,5)
  15. Insert into Testpfx values(123456,9,6,6,9,3)
  16. Insert into Testpfx values(1234567,7,1,7,8,4)
  17. Insert into Testpfx values(12345678,4,2,8,5,8)
  18. Insert into Testpfx values(123456789,1,3,9,2,6)
  19.  
  20.  
  21.  
  22. --Here I am creating a table variable to save the closest match into
  23. declare @tbl table(pfx varchar(20),R1 money,R2 money,R3 money,R4 money,R5 money)
  24.  
  25.  
  26.  
  27.  
  28. --I used your method to get the row that matches closest
  29. --but I save it into a table variable 
  30. --so the humungus query only has to work with 1 row
  31. Declare @No varchar(20) 
  32. Set @No = '124654654' 
  33. insert into @tbl
  34. Select top 1 * from TestPfx Where @No like Pfx + '%' order by Pfx Desc
  35.  
  36.  
  37.  
  38.  
  39. --And here is the humungus query to sort the columns
  40. Select 'PFX' as pfx
  41. ,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
  42. ,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
  43. ,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
  44. ,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
  45. ,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
  46. from
  47. (
  48. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1,'R1' as C1
  49. ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2,'R2' as C2
  50. ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3,'R3' as C3
  51. ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4,'R4' as C4
  52. ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5,'R5' as C5 
  53. from @tbl
  54. )a
  55. union all
  56. Select CONVERT(VARCHAR(10),pfx)
  57. ,CONVERT(VARCHAR(10),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
  58. ,CONVERT(VARCHAR(10),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
  59. ,CONVERT(VARCHAR(10),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
  60. ,CONVERT(VARCHAR(10),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
  61. ,CONVERT(VARCHAR(10),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
  62. from
  63. (
  64. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1,'R1' as C1
  65. ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2,'R2' as C2
  66. ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3,'R3' as C3
  67. ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4,'R4' as C4
  68. ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5,'R5' as C5 
  69. from @tbl
  70. )a
  71.  
May 9 '08 #4

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
  1. declare @Testpfx table
  2. (
  3. pfx varchar(20),
  4. R1 money,
  5. R2 money,
  6. R3 money,
  7. R4 money,
  8. R5 money
  9. )
  10. Insert into @Testpfx values(1,1,7,1,3,9)
  11. Insert into @Testpfx values(12,5,8,2,5,5)
  12. Insert into @Testpfx values(123,8,9,3,7,1)
  13. Insert into @Testpfx values(1234,3,4,4,1,7)
  14. Insert into @Testpfx values(12345,6,5,5,5,5)
  15. Insert into @Testpfx values(123456,9,6,6,9,3)
  16. Insert into @Testpfx values(1234567,7,1,7,8,4)
  17. Insert into @Testpfx values(1235678,4,2,8,5,8)
  18. Insert into @Testpfx values(123456789,1,3,9,2,6)
  19.  
  20. declare @Testpfx2 table (rowid int identity(1,1), pfx varchar(20), rColumns varchar(3), RValues money)
  21.  
  22. insert into @Testpfx2(pfx, rColumns, RValues)
  23. select TOP 100 percent pfx, RColumns, RValues
  24. from 
  25. (select pfx, RColumns, RValues
  26. from
  27. (Select top 1 pfx, r1, r2, r3, r4, r5 from @Testpfx
  28. where patindex('%' + pfx + '%', '124654654') > 0
  29. order by pfx desc) Rs
  30. UNPIVOT
  31. (RValues for RColumns IN 
  32. (r1, r2, r3, r4, r5)
  33. ) as vwUnPivot) vwSorted order by RValues
  34.  
  35.  
  36. select pfx, [r1] as r1, [r2] as r2, [r3] as r3, [r4] as r4, [r5] as r5
  37. from
  38. (select pfx, 'r' + cast(rowid as varchar(15)) as newRColumn, Rvalues from @Testpfx2) vwSource
  39. PIVOT
  40. (
  41. sum(RValues)
  42. for newRColumn in ([r1], [r2], [r3],[r4],[r5])
  43. ) 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
  1. Create Table TestPfx
  2. (
  3. pfx varchar(20),
  4. R1 money,
  5. R2 money,
  6. R3 money,
  7. R4 money,
  8. R5 money,
  9. )
  10. Insert into Testpfx values(1,1,7,1,3,9)
  11. Insert into Testpfx values(12,5,8,2,5,5)
  12. Insert into Testpfx values(123,8,9,3,7,1)
  13. Insert into Testpfx values(1234,3,4,4,1,7)
  14. Insert into Testpfx values(12345,6,5,5,5,5)
  15. Insert into Testpfx values(123456,9,6,6,9,3)
  16. Insert into Testpfx values(1234567,7,1,7,8,4)
  17. Insert into Testpfx values(12345678,4,2,8,5,8)
  18. Insert into Testpfx values(123456789,1,3,9,2,6)
  19.  
  20.  
  21.  
  22. --Here I am creating a table variable to save the closest match into
  23. declare @tbl table(pfx varchar(20),R1 money,R2 money,R3 money,R4 money,R5 money)
  24.  
  25.  
  26.  
  27.  
  28. --I used your method to get the row that matches closest
  29. --but I save it into a table variable 
  30. --so the humungus query only has to work with 1 row
  31. Declare @No varchar(20) 
  32. Set @No = '124654654' 
  33. insert into @tbl
  34. Select top 1 * from TestPfx Where @No like Pfx + '%' order by Pfx Desc
  35.  
  36.  
  37.  
  38.  
  39. --And here is the humungus query to sort the columns
  40. Select 'PFX' as pfx
  41. ,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
  42. ,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
  43. ,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
  44. ,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
  45. ,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
  46. from
  47. (
  48. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1,'R1' as C1
  49. ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2,'R2' as C2
  50. ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3,'R3' as C3
  51. ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4,'R4' as C4
  52. ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5,'R5' as C5 
  53. from @tbl
  54. )a
  55. union all
  56. Select CONVERT(VARCHAR(10),pfx)
  57. ,CONVERT(VARCHAR(10),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
  58. ,CONVERT(VARCHAR(10),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
  59. ,CONVERT(VARCHAR(10),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
  60. ,CONVERT(VARCHAR(10),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
  61. ,CONVERT(VARCHAR(10),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
  62. from
  63. (
  64. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1,'R1' as C1
  65. ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2,'R2' as C2
  66. ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3,'R3' as C3
  67. ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4,'R4' as C4
  68. ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5,'R5' as C5 
  69. from @tbl
  70. )a
  71.  
May 9 '08 #7

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

Delerna
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
  1. ...R1..R2..R3..R4..R5
  2.    4...2...9...1....4
  3.    1...3..0....4....1     count of values higher
  4.    0...0..0....0....1     count of previous columns that are the same
  5. P  1...3...0...4....2
  6.  
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

Delerna
Expert 100+
P: 1,134
Here it is

Expand|Select|Wrap|Line Numbers
  1. 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
  2. ,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
  3. ,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
  4. ,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
  5. ,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
  6. from
  7. (
  8. select pfx,R1, case when R1<R2 THEN 1 else 0 end + case when R1<R3 THEN 1 else 0 end + case when R1<R4 THEN 1 else 0 end + case when R1<R5 THEN 1 else 0 end as P1
  9. ,R2, case when R2<R1 THEN 1 else 0 end + case when R2<R3 THEN 1 else 0 end + case when R2<R4 THEN 1 else 0 end + case when R2<R5 THEN 1 else 0 end + case when R1=R2 then 1 else 0 end as P2
  10. ,R3, case when R3<R1 THEN 1 else 0 end + case when R3<R2 THEN 1 else 0 end + case when R3<R4 THEN 1 else 0 end + case when R3<R5 THEN 1 else 0 end + case when R1=R3 then 1 else 0 end + case when R2=R3 then 1 else 0 end as P3
  11. ,R4, case when R4<R1 THEN 1 else 0 end + case when R4<R2 THEN 1 else 0 end + case when R4<R3 THEN 1 else 0 end + case when R4<R5 THEN 1 else 0 end + case when R1=R4 then 1 else 0 end + case when R2=R4 then 1 else 0 end + case when R3=R4 then 1 else 0 end as P4
  12. ,R5, case when R5<R1 THEN 1 else 0 end + case when R5<R2 THEN 1 else 0 end + case when R5<R3 THEN 1 else 0 end + case when R5<R4 THEN 1 else 0 end + case when R1=R5 then 1 else 0 end + case when R2=R5 then 1 else 0 end + case when R3=R5 then 1 else 0 end + case when R4=R5 then 1 else 0 end as P5 
  13. from @tbl
  14. )a
  15.  

Good luck making it dynamic.
May 10 '08 #10

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

Delerna
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

Post your reply

Sign in to post your reply or Sign up for a free account.