Returning distinict document matching the aggregate MAX result with GROUP BY

query
n1ql

#1

I have a set of score records, say:

{ 
  "userId":"user_123", 
  "score":123.4 
},
{ 
  "userId":"user_123", 
  "score":234.5 
}

And I am selecting them using:

select distinct userId, meta(mybucket).id, max(score) as maxscore from mybucket 
where score is not null and score is not missing
group by userId
order by score desc

The results returned will return a record with correct max(score) for the distinct userId, but, the meta(mybucket).id and any other fields unique per record will not be from the record with max score - they will be from any record in the set of records for that distinct userId.

How can I return only one record where score matches the max(score) for distinct userId?

Here is a live example using tutorial:

There are two records with lname ‘Jackson’:

select fname, lname, age from tutorial where lname like 'Jackson'

{
  "results": [
    {
      "age": 18,
      "fname": "Fred",
      "lname": "Jackson"
    },
    {
      "age": 20,
      "fname": "Harry",
      "lname": "Jackson"
    }
  ]
}

When I select with grouping by lname and max(age) i get expected result. Except distinct will return any one of the records within the set. So Fred is actually 18, but is returned here by distinict.

select distnict lname, fname, max(age) as maxage from tutorial 
group by lname 
order by age desc

{
  "results": [
    {
      "fname": "Ian",
      "maxage": 56
    },
    {
      "fname": "Dave",
      "maxage": 46
    },
    {
      "fname": "Earl",
      "maxage": 46
    },
    {
      "fname": "Jane",
      "maxage": 40
    },
    {
      "fname": "Fred",
      "maxage": 20
    }
  ]
}

How can I make sure that the returned result is pulling fname from the record with max(age) instead of one of the records from the group by set?


#2

Hi @enko, you can do the following:

SELECT userId, MAX( [ score, META(mybucket).id ] ) AS pair
FROM mybucket
WHERE score IS NOT NULL
GROUP BY userId
ORDER BY pair DESC;

#3

Gerald thank you!

Ended up with:

select userId, 
max([score,meta(mybucket).id])[0] as score,
max([score,meta(mybucket).id])[1] as scoreId,
nick
from mybucket
where score is not null and app = 'my_app' 
group by userId
order by score desc
limit 33 offset 67