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]

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

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

[Gert-Jan Strik]

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:

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]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

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

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]

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

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<3f********@news.bluewin.ch>...

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

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

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

[Anith Sen]

>> 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]

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:

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

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<3f********@news.bluewin.ch>...

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...
> 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

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