Advance ORDER BY

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."Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0309071031.7f6b9826@.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|||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" <sql@.hayes.ch> wrote in message
news:3f5b7da7$1_3@.news.bluewin.ch...
> "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> news:fc254714.0309071031.7f6b9826@.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|||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>...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:3f5b7da7$1_3@.news.bluewin.ch...
> > "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> > news:fc254714.0309071031.7f6b9826@.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|||>> 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 )|||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" <sql@.hayes.ch> wrote in message news:<3f5b822c_4@.news.bluewin.ch>...
> > "Simon Hayes" <sql@.hayes.ch> wrote in message
> > news:3f5b7da7$1_3@.news.bluewin.ch...
> > > > "Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
> > > news:fc254714.0309071031.7f6b9826@.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

ADO find method too slow, how should I do this

I need an efficient way to get the absolute position of a record in a query matching a specific key value. The Find method is a serial search, too slow for big data sets. I am using both SQL Server Express and Jet 4 via ADO.

One example of why I need this .....

I have a list control with a subset of a table (controlled by where clause in query). I want to save the current state of the list control and later restore it when the app restarts. I want to preserve and restore the current line selection in the list control.

So, I save the key value for the current line, upon restart use the find method to locate the key, and set the list control current record index to the current absolute position.

This is too slow for big data sets since the find does a record by record search.

I cannot just save and restore the list control offset, the table may have changed.

The list control has owner data so the data is not all read into the control, so I can't just search through the controls image.

Any ideas. I did search for this answer and failed. Feel free to flame me as long as an answer is included too :-)

Z

Hi,

Here's a quick idea: instead of doing the Find, why don't you simply run a query like "SELECT COUNT(*) FROM mytable WHERE tablekey <= mycurrentkey", this would give you the desired "absolute position" in the recordset and you could simply manipulate the selection in the listbox.

I understand this may go out of sync with the original table, but you could mitigate that - for instance, enclosing both the listbox population and the query above into a transaction (probably with higher isolation level).

And one more (simpler) idea - many of the list and combo-box controls have their own Seek methods. You can use that and position into the control instead of going through the recordset. The search will be local into the already populated data.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another idea that might work is manually do a binary search on the keys in the listbox (if the items in the listbox are sorted by key this will work). Should be faster than find.

Likewise if your key is ordered you could do what Jivko mentions but something like this:

select count(*) from table where <conditions that restrict items> and key=<saved pkey from last time>

Count should give you your absolute position or zero if the record is deleted.