Working with complex multiple ordering the data

Question

Possible Duplicate:
Need help for complex data sort SQL Server

this is my data in sql server which i need to sort and display

Table : MyTable
------------------
ID  Title
-----------
1   Geo Prism GEO 1995 GEO* - ABS #16213899
2   Excavator JCB - ECU P/N: 728/35700
3   Geo Prism GEO 1995 - ABS #16213899
4   JCB Excavator JCB- ECU P/N: 728/35700
5   Geo Prism GEO,GEO 1995 - ABS #16213899 GEO
6   Maruti gear box #ABS 4587

now i want to sort data based on the search term like GEO & JCB

those rows will come first where GEO or JCB found maximum time here GEO found in 3 rows and JCB found in 2 rows. so all rows have GEO keyword those will come at top and next JCB related rows will come. unmatch row will come at last.

again there will be sorting. in GEO related rows...those rows will come first which has maximum GEO keyword. the same JCB related rows will be sorted.

here i am giving the images which will show what kind of out i need

enter image description here i asked this question and got answer which does not full fill my requirement fully. so here is the sql i got for this question.

CREATE FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))       
as       
begin       
declare @idx int       
declare @slice varchar(8000)       

select @idx = 1       
    if len(@String)<1 or @String is null  return       

while @idx!= 0       
begin       
   set @idx = charindex(@Delimiter,@String)       
   if @idx!=0       
       set @slice = left(@String,@idx - 1)       
   else       
      set @slice = @String       

   if(len(@slice)>0)  
       insert into @temptable(Items) values(@slice)       

   set @String = right(@String,len(@String) - @idx)       
   if len(@String) = 0 break       
end   
return       
end

DECLARE @Sterm varchar(MAX) 
SET @Sterm ='GEO JCB'
;WITH SearchResult (rnum, title)
as 
(   
(select 1 as rnum,'Geo Prism GEO 1995 GEO* - ABS #16213899' as title)
union all
(select 2 as rnum,'Excavator JCB - ECU P/N: 728/35700' as title)
union all
(select 3 as rnum,'Geo Prism GEO 1995 - ABS #16213899' as title)
union all
(select 4 as rnum,'JCB Excavator JCB- ECU P/N: 728/35700' as title)
union all
(select 5 as rnum,'Geo Prism GEO,GEO 1995 - ABS #16213899 GEO' as title)
union all
(select 6 as rnum,'dog' as title)
) 

select rnum, title from SearchResult
join 
( select lower(Items) as term 
  from dbo.Split(@Sterm , ' ')
) as search_terms
on lower(SearchResult.title) like '%' + search_terms.term +'%'
order by 
search_terms.term,
(select count(*)
from dbo.Split(lower(SearchResult.title),' ')
where Items = search_terms.term
) desc

NikRED · Answer 1 · 2012-08-08T15:24:33.923

declare @SearchItem varchar(1000)='GEO,JCB'

declare @Instring varchar(2000)='' select @Instring=@Instring+' when Title like ''%'+items+'%'' then '''+items+'''' from dbo.split(@SearchItem,',') exec(' declare @tbl table(id int,title varchar(200)) ; with CTE as ( select 1 as id, ''Geo Prism GEO 1995 GEO* - ABS #16213899'' as Title union select 2 as id, ''Excavator JCB - ECU P/N: 728/35700'' as Title union select 3 as id, ''Geo Prism GEO 1995 - ABS #16213899'' as Title union select 4 as id, ''JCB Excavator JCB- ECU P/N: 728/35700'' as Title union select 5 as id, '' Geo Prism GEO,GEO 1995 - ABS #16213899 GEO'' as Title union select 6 as id, ''Maruti gear box #ABS 4587'' as Title)

insert into @tbl select id,Title from CTE

declare @tbl2 table(id int,T int,title varchar(200),Tl varchar(200),CC int) insert into @tbl2 (id,T,title,Tl) select id,T,title,Tl from(select tb.id,sub.T,tb.title ,Tl from (select case '+@Instring +' else ''Other'' END as Tl ,count(*)T from @tbl group by case '+@Instring +' else ''Other'' END)Sub
join @tbl tb on tb.Title like ''%''+sub.Tl +''%'' )Sub1

declare @Ttle varchar(max) declare @Tl varchar(max) declare @id int declare @i int=0 declare @Nbr int=0 declare Cursr Cursor for select title,id,Tl from @tbl2
open Cursr Fetch next from Cursr into @Ttle,@id,@Tl while @@FETCH_STATUS =0 begin while (@i<=len(@Ttle)) begin if charindex(@Tl,@Ttle)!=0 set @Nbr =@Nbr +1 set @Ttle =stuff(@Ttle,charindex(@Tl,@Ttle),len(@Tl),'''') set @i=@i+1 end

     update @tbl2 set cc=@Nbr where id=@id 
     set @Nbr =0

Fetch next from Cursr into @Ttle,@id,@Tl end

select id,T,title,Tl,CC from
(select id,T,title,Tl,CC from @tbl2
union select id,0 as T,title,'''' as Tl,0 as CC from @tbl where id not in (select id from @tbl2 ))S1 order by T desc,CC desc ' )

here u hard code the search term but i want no hard code data. — Thomas, Aug 07 '12 at 09:14
Hi, I have edited the code,Please create the split function which you have written in your question and replace the CTE with your table — NikRED, Aug 08 '12 at 16:03

Working with complex multiple ordering the data

1 Answers1