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