Find docs with "largest" value per (or grouped by) a category

SQL++
1

Hi

I’m trying to build a query that will return the “largest” catch (length) per species for a specific user.

So a Catch looks like this:

I have this query working for one species, e.g. species ‘1’:

SELECT fishingtripkey,specieskey,length FROM data WHERE type='Catch' 
and userkey='BA171123846CEBF1C1257CB2002DA330' and specieskey='1'
order by length desc 
limit 1

which returns:

[
  {
    "fishingtripkey": "07964457775CA4B5C1257CA700355976",
    "length": 154,
    "specieskey": "1"
  }
]

… and species ‘2’

SELECT fishingtripkey,specieskey,length FROM data WHERE type='Catch' 
and userkey='BA171123846CEBF1C1257CB2002DA330' and specieskey='2'
order by length desc 
limit 1

Returns:

[
  {
    "fishingtripkey": "550d5d83f6533e13b90bf9ce",
    "length": 56,
    "specieskey": "2"
  }
]

However, I would like to fire the query for all catches in one query and find the largest catch for each species - and I need the specific document back so I can get the fishingtripkey to create a link to that trip.

I have tried to use group by.... - but it only works on aggregated data so I have not been able to find a way to get what I want. Any ideas?

I’m on Community edition 5.1.1

2 
SELECT specieskey ,  m[0] AS length, m[1] AS fishingtripkey
FROM data 
WHERE type='Catch' AND userkey='BA171123846CEBF1C1257CB2002DA330'
GROUP BY specieskey
LETTING m = MAX([length,fishingtripkey]);

Read Select non-group by fields using sql query for understanding the technique.

3

Cool!

I knew it could be done! Thanks :+1: