Hi,
Best match is "9854002656" among the 2 records.
Pls try this simple one.It will make it clear,
create table test(t text);
insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');
select * from test where '982' like t || '%' order by t desc limit 1;
above sql tries to model a DST(digit search tree).
Expected output: 98
This works but will not work for earlier data(9854002656).So seems to be bug.
regards
ragha
******************************************************************************************
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*****************************************************************************************
----- Original Message -----
From: John Machin <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 6:22 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> On 29/08/2007 10:37 PM, RaghavendraK 70574 wrote:
> > Thx. I have modifed it to ?, but
> > Sqlite fails to get records for the below query. When debug it
> retuns
> > SQLITE_DONE. Pls help.
> >
> > select * from 'tbl.7' where ? like column1 || '%' order by
> column1 desc limit 1;
> >
> > Data is as below:
> > Version: 3.4.0
> > Re-confirm the problem in sqlite and not in my code,
> > I tried using sqlite3 commandLine tool and found the same problem.
> >
> > create table 'tbl.7'(ver integer,
> > column1 text not NULL,
> > column2 text not NULL,
> > column3 text not NULL,
> > column4 text not NULL,
> > column5 text not NULL,
> > column6 text not NULL,
> > column7 text not NULL,
> > column8 text not NULL,
> > column9 text not NULL,
> > column10 text not NULL,
> > primary
> key(ver,column1,column2,column3,column4,column5));>
> >
> > insert into 'tbl.7'
> > values
> > (7,
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> >
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',>
>
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',
> >
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',>
>
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',
> >
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL');>
>
> > insert into 'tbl.7'
> > values
> > (7,
> > '9854002656',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> >
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',>
>
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',
> >
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',>
>
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL',
> >
> 'COLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL');>
>
> > --Best match for 985
> > select * from 'tbl.7' where '985' like column1 || '%' order by
> column1 desc limit 1;
>
> Can you leave out the incredibly annoying 'COLLLLL...' stuff? It's
> nothing to do with your problem!
>
> I don't see how *ANY* of your rows will match. Which rows do you
> expect
> to match? Why?
>
> If column1 was '98' (for example), then you would have
> '985' like '98' || '%'
> which is
> '985' like '98%'
> which is true.
>
>
> --------------------------------------------------------------------
> ---------
> To unsubscribe, send email to [EMAIL PROTECTED]
> --------------------------------------------------------------------
> ---------
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
