Best match query and order by columns

[ankitmathur]

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

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

[ck9663]

Option 1:
There's always CURSOR. It could be slow, though.

Option 2:
This one will work in SQL 2005 and up ONLY.

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

[Delerna]

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

[code=sql]
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(12345678 9,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<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
,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
,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
,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
,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
from @tbl
)a
union all
Select CONVERT(VARCHAR (10),pfx)
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
from
(
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
,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
,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
,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
,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
from @tbl
)a
[/code]

[ankitmathur]

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

[code=sql]
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(12345678 9,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<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
,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
,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
,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
,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
from @tbl
)a
union all
Select CONVERT(VARCHAR (10),pfx)
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
from
(
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
,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
,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
,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
,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
from @tbl
)a
[/code]

[ankitmathur]

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.

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

[ankitmathur]

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

[ankitmathur]

Please clarify a few points

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

[code=sql]
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(12345678 9,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<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
,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
,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
,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
,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
from @tbl
)a
union all
Select CONVERT(VARCHAR (10),pfx)
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
,CONVERT(VARCHA R(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
from
(
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
,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
,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
,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
,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
from @tbl
)a
[/code]

[ankitmathur]

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

[Delerna]

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
[code=text]
...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
[/code]

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 :)

[Delerna]

Here it is

[code=sql]
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<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
,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
,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
,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
,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
from @tbl
)a
[/code]


Good luck making it dynamic.

[ankitmathur]

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

[Delerna]

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!