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

public Cursor fetchAllPBs() {       
    String sql = "SELECT * FROM " + CAPTURES_TABLE + " GROUP BY " + KEY_CAPTURES_SPECIES 
            + " ORDER BY " + 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 the above query to return me the user's personal best for each species, that is, the heaviest item within each species. Testing it properly recently I've realised a problem. I'm still relatively new to SQL with this project...

Say I add a 'Chub' to my database of 7lb 6oz 0drms, then add another of 7lb 2oz 0drms - it will return the more recently added fish as the PB and not the biggest (the 7lb 2oz one). However if I then add another Chub of 8lb 0oz 0drms it will return the 8lb fish - it seems it's not properly orderi开发者_运维百科ng them by the Ounces and probably by that I assume the drams too.

Can anyone see what's wrong here and suggest a solution?

Many thanks

First, you need a subquery to determine the heaviest fish per species. Second, your weight is split in 3 columns, so you need to add them in someway. I choose to just add them with multiplication, should be sufficient for getting the max.

SELECT *
FROM CAPTURES_TABLE AS C1
WHERE (100*Pounds+10*Ounces+Drams) = 
      (SELECT MAX(100*Pounds+10*Ounces+Drams) 
      FROM CAPTURES_TABLE AS C2
      WHERE C2.SPECIES=C1.SPECIES)
ORDER BY pounds DESC, ounces DESC, drams DESC

More of a comment, but I need more space...

If I simplify your query it looks like:

SELECT * FROM table1 WHERE species = species_id 
ORDER BY pounds DESC, ounces DESC, drams DESC LIMIT 1

Provided that species is not a unique field, the query is fine.

It looks like the problem is that when adding a new fish you are not adding, but actually replacing the fish, try and remove the limit 1 and see if all fish show up.
The ordering clauses are definitly correct.