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
