December 7, 2006 at 2:58 pm
Derived table to assemble the ranking, outer query on the derived table to find the max rank for each group and sort on it:
Select ItemNumber, PL, Max(Rank)
From
(
select rank, As ItemNumber, PL
From freetexttable (dbo.items,*,'Key Words') as ft
join Items as I on ft.[Key] = I.ItemNumber
LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber
Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')
union All
select '999999',ItemNumber,PL
from View_Items_Published
Where (Mfg_Part_Number like '%Key Words%')
union All
select '999998',ItemNumber,PL
from View_Items_Published
Where (ItemNumber like '%Key Words%')
) dt
Group By ItemNumber, PL
Order By Max(Rank) Desc
December 7, 2006 at 6:33 pm
you are unbelievable
Thank you so much, you have no idea how great this is.
You rock.....
Oren Levy
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply