Databases
StackOverflow2013
Postsdbo
POWorking with complex multiple ordering the data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWorking with complex multiple ordering the data
    primarykey
    data
    text
    <blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/11683134/need-help-for-complex-data-sort-sql-server">Need help for complex data sort SQL Server</a> </p> </blockquote> <p>this is my data in sql server which i need to sort and display</p> <pre><code>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 </code></pre> <p>now i want to sort data based on the search term like GEO &amp; JCB</p> <p>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.</p> <p>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.</p> <p>here i am giving the images which will show what kind of out i need</p> <p><img src="https://i.stack.imgur.com/83xkC.png" alt="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.</p> <pre><code>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)&lt;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)&gt;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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USThomas
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWorking with complex multiple ordering the data
      1. This table or related slice is empty.
      1. VTClose
    2. VO
      singulars
      1. POWorking with complex multiple ordering the data
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWorking with complex multiple ordering the data
      1. This table or related slice is empty.
      1. VTClose
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload