Advance ORDER BY

[Doug Baroter]

Hi,

I'm wondering if there's any advance order by 'function'/workaround,
which is reasonably efficient for MS SQL Server, without resorting to
some third party indexing/search engine to achieve the following.

The mechanism is to record each instance of a pattern match and order
by rows with most matches first (DESC). Simplistic match but that's a
separate issue.

Sample:
create table tmp (col varchar(50));
insert into tmp
values ('a barking dog');
insert into tmp
values ('a dog and cat fights over dog food');
insert into tmp
values ('lovable dog is not barking dog=nice dog');

The goal for the Sample is to return resultsets in the following
order:
lovable dog is not barking dog=nice dog -- 3 matches
a dog and cat fights over dog food -- 2 matches
a barking dog -- 1 match

Thanks.

[Simon Hayes]

Re: Advance ORDER BY


"Doug Baroter" <qwert12345@box frog.com> wrote in message
news:fc254714.0 309071031.7f6b9 826@posting.goo gle.com...[color=blue]
> Hi,
>
> I'm wondering if there's any advance order by 'function'/workaround,
> which is reasonably efficient for MS SQL Server, without resorting to
> some third party indexing/search engine to achieve the following.
>
> The mechanism is to record each instance of a pattern match and order
> by rows with most matches first (DESC). Simplistic match but that's a
> separate issue.
>
> Sample:
> create table tmp (col varchar(50));
> insert into tmp
> values ('a barking dog');
> insert into tmp
> values ('a dog and cat fights over dog food');
> insert into tmp
> values ('lovable dog is not barking dog=nice dog');
>
> The goal for the Sample is to return resultsets in the following
> order:
> lovable dog is not barking dog=nice dog -- 3 matches
> a dog and cat fights over dog food -- 2 matches
> a barking dog -- 1 match
>
> Thanks.[/color]

Here's one possibility:

select col
from tmp
order by len(replace(col , 'dog', '')) desc

Simon

[Gert-Jan Strik]

Re: Advance ORDER BY

A slight correction on Simon's solution (which will return the longest
string, regardless of number of matches)

select col
from tmp
order by len(col) - len(replace(col , 'dog', '')) desc

Gert-Jan


Doug Baroter wrote:[color=blue]
>
> Hi,
>
> I'm wondering if there's any advance order by 'function'/workaround,
> which is reasonably efficient for MS SQL Server, without resorting to
> some third party indexing/search engine to achieve the following.
>
> The mechanism is to record each instance of a pattern match and order
> by rows with most matches first (DESC). Simplistic match but that's a
> separate issue.
>
> Sample:
> create table tmp (col varchar(50));
> insert into tmp
> values ('a barking dog');
> insert into tmp
> values ('a dog and cat fights over dog food');
> insert into tmp
> values ('lovable dog is not barking dog=nice dog');
>
> The goal for the Sample is to return resultsets in the following
> order:
> lovable dog is not barking dog=nice dog -- 3 matches
> a dog and cat fights over dog food -- 2 matches
> a barking dog -- 1 match
>
> Thanks.[/color]

[Simon Hayes]

Re: Advance ORDER BY


"Simon Hayes" <sql@hayes.ch > wrote in message
news:3f5b7da7$1 _3@news.bluewin .ch...[color=blue]
>
> "Doug Baroter" <qwert12345@box frog.com> wrote in message
> news:fc254714.0 309071031.7f6b9 826@posting.goo gle.com...[color=green]
> > Hi,
> >
> > I'm wondering if there's any advance order by 'function'/workaround,
> > which is reasonably efficient for MS SQL Server, without resorting to
> > some third party indexing/search engine to achieve the following.
> >
> > The mechanism is to record each instance of a pattern match and order
> > by rows with most matches first (DESC). Simplistic match but that's a
> > separate issue.
> >
> > Sample:
> > create table tmp (col varchar(50));
> > insert into tmp
> > values ('a barking dog');
> > insert into tmp
> > values ('a dog and cat fights over dog food');
> > insert into tmp
> > values ('lovable dog is not barking dog=nice dog');
> >
> > The goal for the Sample is to return resultsets in the following
> > order:
> > lovable dog is not barking dog=nice dog -- 3 matches
> > a dog and cat fights over dog food -- 2 matches
> > a barking dog -- 1 match
> >
> > Thanks.[/color]
>
> Here's one possibility:
>
> select col
> from tmp
> order by len(replace(col , 'dog', '')) desc
>
> Simon
>
>[/color]

Sorry - I posted that a bit too quickly. It should be this - the division by
3 is because your search term has 3 characters, so you can count the number
of replacements made this way:

select col
from tmp
order by (len(col) - len(replace(col , 'dog', ''))) / 3 desc

Simon

[Doug Baroter]

Re: Advance ORDER BY

Thank you. Gert-Jan's solution also works.
Can you explain why?

"Simon Hayes" <sql@hayes.ch > wrote in message news:<3f5b822c_ 4@news.bluewin. ch>...[color=blue]
> "Simon Hayes" <sql@hayes.ch > wrote in message
> news:3f5b7da7$1 _3@news.bluewin .ch...[color=green]
> >
> > "Doug Baroter" <qwert12345@box frog.com> wrote in message
> > news:fc254714.0 309071031.7f6b9 826@posting.goo gle.com...[color=darkred]
> > > Hi,
> > >
> > > I'm wondering if there's any advance order by 'function'/workaround,
> > > which is reasonably efficient for MS SQL Server, without resorting to
> > > some third party indexing/search engine to achieve the following.
> > >
> > > The mechanism is to record each instance of a pattern match and order
> > > by rows with most matches first (DESC). Simplistic match but that's a
> > > separate issue.
> > >
> > > Sample:
> > > create table tmp (col varchar(50));
> > > insert into tmp
> > > values ('a barking dog');
> > > insert into tmp
> > > values ('a dog and cat fights over dog food');
> > > insert into tmp
> > > values ('lovable dog is not barking dog=nice dog');
> > >
> > > The goal for the Sample is to return resultsets in the following
> > > order:
> > > lovable dog is not barking dog=nice dog -- 3 matches
> > > a dog and cat fights over dog food -- 2 matches
> > > a barking dog -- 1 match
> > >
> > > Thanks.[/color]
> >
> > Here's one possibility:
> >
> > select col
> > from tmp
> > order by len(replace(col , 'dog', '')) desc
> >
> > Simon
> >
> >[/color]
>
> Sorry - I posted that a bit too quickly. It should be this - the division by
> 3 is because your search term has 3 characters, so you can count the number
> of replacements made this way:
>
> select col
> from tmp
> order by (len(col) - len(replace(col , 'dog', ''))) / 3 desc
>
> Simon[/color]

[Anith Sen]

Re: Advance ORDER BY

>> Thank you. Gert-Jan's solution also works. Can you explain why? <<

Take the expression used in the ORDER BY clause and add it in the SELECT
list. The answer then becomes obvious.

--
- Anith
( Please reply to newsgroups only )

[Gert-Jan Strik]

Re: Advance ORDER BY

Of course it does :-)

It works because for the ORDER BY clause you do not need the actual
number of occurrences. You just need them sorted. In that respect "1
barking", "2 dog and cat", "3 lovable dog" is the same as "3 barking",
"6 dog and cat", "9 lovable dog".

Gert-Jan


Doug Baroter wrote:[color=blue]
>
> Thank you. Gert-Jan's solution also works.
> Can you explain why?
>
> "Simon Hayes" <sql@hayes.ch > wrote in message news:<3f5b822c_ 4@news.bluewin. ch>...[color=green]
> > "Simon Hayes" <sql@hayes.ch > wrote in message
> > news:3f5b7da7$1 _3@news.bluewin .ch...[color=darkred]
> > >
> > > "Doug Baroter" <qwert12345@box frog.com> wrote in message
> > > news:fc254714.0 309071031.7f6b9 826@posting.goo gle.com...
> > > > Hi,
> > > >
> > > > I'm wondering if there's any advance order by 'function'/workaround,
> > > > which is reasonably efficient for MS SQL Server, without resorting to
> > > > some third party indexing/search engine to achieve the following.
> > > >
> > > > The mechanism is to record each instance of a pattern match and order
> > > > by rows with most matches first (DESC). Simplistic match but that's a
> > > > separate issue.
> > > >
> > > > Sample:
> > > > create table tmp (col varchar(50));
> > > > insert into tmp
> > > > values ('a barking dog');
> > > > insert into tmp
> > > > values ('a dog and cat fights over dog food');
> > > > insert into tmp
> > > > values ('lovable dog is not barking dog=nice dog');
> > > >
> > > > The goal for the Sample is to return resultsets in the following
> > > > order:
> > > > lovable dog is not barking dog=nice dog -- 3 matches
> > > > a dog and cat fights over dog food -- 2 matches
> > > > a barking dog -- 1 match
> > > >
> > > > Thanks.
> > >
> > > Here's one possibility:
> > >
> > > select col
> > > from tmp
> > > order by len(replace(col , 'dog', '')) desc
> > >
> > > Simon
> > >
> > >[/color]
> >
> > Sorry - I posted that a bit too quickly. It should be this - the division by
> > 3 is because your search term has 3 characters, so you can count the number
> > of replacements made this way:
> >
> > select col
> > from tmp
> > order by (len(col) - len(replace(col , 'dog', ''))) / 3 desc
> >
> > Simon[/color][/color]