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
