I tried without AS but still get same error : SQL error: no such column: f.type Maybe sqlite 2.8.17 can't run that query
On Tue, Dec 9, 2008 at 1:50 PM, Jonas Sandman <[EMAIL PROTECTED]> wrote: > Shouldn't it be > > select f.type, f.variety, f.price > from > fruits f > where > rowid in (select rowid from fruits where type = f.type order by > price desc limit 1) > > ? > > No need for the 'AS' there. > > /Jonas > > On Tue, Dec 9, 2008 at 12:54 AM, Hariyanto Handoko <[EMAIL PROTECTED]> wrote: >> I want to get one result from that query. >> It ok if run under sqlite3. >> >> But when I try with with sqlite 2.8.17, I got this error. >> SQL error: no such column: f.type >> I tried add AS (Alias) but still got that errror message. >> >> select f.type, f.variety, f.price >> from >> fruits AS f >> where >> rowid in (select rowid from fruits where type = f.type order by >> price desc limit 1) >> >> order by f.type asc, f.price desc; >> >> I got from this old message : ( >> Re: [sqlite] Select the top N rows from each group) >> >> create table fruits (type text, variety text, price number); >> create index fruit_type_price on fruits (type, price); >> insert into fruits values ('apple', 'gala', 2.79); >> insert into fruits values ('apple', 'fuji', 0.24); >> insert into fruits values ('apple', 'limbertwig', 2.87); >> insert into fruits values ('orange', 'valencia', 3.59); >> insert into fruits values ('orange', 'navel', 9.36); >> insert into fruits values ('pear', 'bradford', 6.05); >> insert into fruits values ('pear', 'bartlett', 2.14); >> insert into fruits values ('cherry', 'bing', 2.55); >> insert into fruits values ('cherry', 'chelan', 6.33); >> >> result from query sqlite3 : >> apple|limbertwig|2.87 >> cherry|chelan|6.33 >> orange|navel|9.36 >> pear|bradford|6.05 >> >> How I can fix that sql so I can get same result in sqlite 2.8.17 >> tx >> >> >> On Tue, Dec 9, 2008 at 2:12 AM, Martin Engelschalk >> <[EMAIL PROTECTED]> wrote: >>> >>> Hi, >>> >>> i thought that too, at first, but i think he wants to select one record >>> for every type of fruit, namely the record with the highest price within >>> one value of 'type' (I have difficulty saying what I mean in english.) >>> >>> However, the longer i look at the statement, the less sense it makes to >>> me. Perhaps Hariyanto Handoko would like to post what the statement is >>> supposed to achieve. >>> >>> Martin >>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users