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
____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
