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 Ribeiro, Glauber wrote: > This should return only one record, right? So you shouldn't need the > second order by ("order by f.type asc, f.price desc"). > > -----Original Message----- > From: Martin Engelschalk [mailto:[EMAIL PROTECTED] > Sent: Monday, December 08, 2008 8:15 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] convert sql for sqlite 2.8.17 > > Hi, > > I don't know what your problem is, exactly. However, I suspect that you > have to alias the second "fruits" as well: > > select f.type, f.variety, f.price > from > fruits f > where > f.rowid in (select f1.rowid from fruits f1 where f1.type = f.type > order by > f2.price desc limit 1) > order by f.type asc, f.price desc; > > Martin > > Hariyanto Handoko wrote: > >> I still sqlite 2.8.17. Can someone help me to convert this sql? >> tx >> >> 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) >> order by f.type asc, f.price desc; >> _______________________________________________ >> 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