android - SQLite order by 3 fields and return top 1 for each category -

public cursor fetchallpbs() {            string sql = "select * " + captures_table + " group " + key_captures_species              + " order " + key_captures_pounds + " desc, " + key_captures_ounces + " desc, " + key_captures_drams + " desc;";     cursor c = mdb.rawquery(sql, null);     if(c != null) {         c.movetofirst();     }     return c; } 

hi,

i want above query return me user's personal best each species, is, heaviest item within each species. testing i've realised problem. i'm still relatively new sql project...

say add 'chub' database of 7lb 6oz 0drms, add of 7lb 2oz 0drms - return more added fish pb , not biggest (the 7lb 2oz one). if add chub of 8lb 0oz 0drms return 8lb fish - seems it's not ordering them ounces , assume drams too.

can see what's wrong here , suggest solution?

many thanks

first, need subquery determine heaviest fish per species. second, weight split in 3 columns, need add them in someway. choose add them multiplication, should sufficient getting max.

select * captures_table c1 (100*pounds+10*ounces+drams) =        (select max(100*pounds+10*ounces+drams)        captures_table c2       c2.species=c1.species) order pounds desc, ounces desc, drams desc