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
