Time to upgrade.
--- Christopher Backhouse <[EMAIL PROTECTED]> wrote:
> I repeated exactly what you have there and confirm it doesn't work for me.
>
> [EMAIL PROTECTED]:/tmp$ sqlite3 testing
> SQLite version 3.3.13
> Enter ".help" for instructions
> sqlite> CREATE TABLE foo(bar text, baz text);
> sqlite> INSERT INTO "foo" VALUES('123','345');
> sqlite> INSERT INTO "foo" VALUES('123','6789');
> sqlite> INSERT INTO "foo" VALUES('23456','13');
> sqlite> INSERT INTO "foo" VALUES('432','13');
> sqlite> INSERT INTO "foo" VALUES('7654321','13');
> sqlite> INSERT INTO "foo" VALUES('1234567','345');
> sqlite> select * from foo;
> 123|345
> 123|6789
> 23456|13
> 432|13
> 7654321|13
> 1234567|345
> sqlite> select distinct bar, baz as z from foo where bar in (select bar
> from foo where baz==z order by length(bar) desc limit 1);
> sqlite> .q
>
>
> Joe Wilson wrote:
> > --- Christopher Backhouse <[EMAIL PROTECTED]> wrote:
> >> I have a table:
> >> create table foo(bar text,baz text);
> >>
> >> I want to produce a list of all bar's paired with the longest baz they
> >> are ever associated with. I came up with the following query:
> >>
> >> select distinct bar,baz as z from foo where bar in (select bar from foo
> >> where baz==z order by length(bar) desc limit 1);
> >>
> >> This query works (although it might not be the best way to do this),
> >> except in the case where bar is a string that can be interpreted as a
> >> number in which case that value of bar never appears in the output.
> >> I assume that in one part of the query it is being treated as a number
> >> and at another as a string, such that they compare not-equal.
> >>
> >> Shouldn't the "text" in the table definition cause promotion to string
> >> in every case?
> >>
> >> My question is how do i cast bar back to a string, or otherwise cause
> >> sqlite to do the right thing in this case? Or alternatively is there a
> >> different query which would avoid this problem?
> >
> > I don't see the problem.
> > Are you seeing something different?
> >
> > SQLite version 3.4.0
> >
> > CREATE TABLE foo(bar text, baz text);
> > INSERT INTO "foo" VALUES('123','345');
> > INSERT INTO "foo" VALUES('123','6789');
> > INSERT INTO "foo" VALUES('23456','13');
> > INSERT INTO "foo" VALUES('432','13');
> > INSERT INTO "foo" VALUES('7654321','13');
> > INSERT INTO "foo" VALUES('1234567','345');
> >
> > select distinct bar, baz as z from foo where bar in (
> > select bar from foo where baz==z
> > order by length(bar) desc limit 1);
> >
> > bar z
> > ---------- ----------
> > 123 6789
> > 7654321 13
> > 1234567 345
____________________________________________________________________________________
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news,
photos & more.
http://mobile.yahoo.com/go?refer=1GNXIC
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
