A little N1QL challenge with group by

SQL++
1

I have a (simplified) data structure like this:

{
  "date": "2019-12-11T00:00:00+0100",
  "type": "Catch",
  "userkey": "2124DEFEC111BA8FC1257ED20034B387",
  "key": "0d3a99d71b5e43e39ff18823816d6fd5",
  "specieskey": "38",
  "count": 1,
  "length": 56,
  "weight": 2.3,
}

Now, I would like to find the longest or heaviest catch (a fish) from the above. I have either length or weight or both specified for each catch. If a catch is more than one then I can use length (as longest) but not weight. Once found I would like to have the key for the catch - and the weight for the longest catch. Oh, and I want find these grouped by species :slight_smile:

I have this query right now that will give me the longest (or heaviest - if no catches of this species had a length):

So the “catch” is that if any of the catches has a length then we want to return the longest of those (with the corresponding weight - although other catches without length could be heavier).

SELECT specieskey,IFMISSINGORNULL(max(length),max(case when count = 1 then weight else null end)) as maxlength
FROM data 
WHERE type='Catch' and userkey='2124DEFEC111BA8FC1257ED20034B387'
group by specieskey

Can I get the extra info out of the query?

2 
SELECT specieskey, CASE WHEN m[0] != 0 THEN m[0] ELSE m[1] END AS maxlength
FROM data 
WHERE type='Catch' and userkey='2124DEFEC111BA8FC1257ED20034B387' 
GROUP BY specieskey
LETTING m = MAX([IFMISSINGORNULL(length,0), CASE WHEN `count` = 1 THEN weight ELSE NULL END]);
3

Probably a better (or cleaner) way to do what I do. But it gives the same result…

If I have this dataset:

  {
    "count": 1,
    "key": "65F6C7C0B9B47580C1257CB60059FD8F",
    "specieskey": "7",
    "weight": 3
  },
  {
    "count": 1,
    "key": "62B179318A1D0AE6C1257CB4006E152F",
    "length": 55,
    "specieskey": "7",
    "weight": 2.55
  }

then I would like to have returned the one with length 55 and its weight 2.55 (and key - for allowing the app to link to it) - although there is another one that is heavier - but it is another fish, and we prioritize length over weight :slight_smile:

4

This returns right data

SELECT specieskey, CASE WHEN m[0] != 0 THEN m[0] ELSE m[1] END AS maxlength, m[1] AS weight, m[2] AS key1
FROM data
WHERE type='Catch' and userkey='2124DEFEC111BA8FC1257ED20034B387'
GROUP BY specieskey
LETTING m = MAX([IFMISSINGORNULL(length,0), CASE WHEN `count` = 1 THEN weight ELSE NULL END, `key`]);
5

Cool!

I really just need to return null instead of m[1] to not return weight in the “maxlength”. I had tried to do something similar but didn’t manage to get the LETTING... statement quite right.

Thank you very much!