Select non-group by fields using sql query

SQL++
3

To expand on @vsr1’s answer, here is the reply I posted on StackOverflow:

This can be answered using ‘group by’ and no join. I tried entering your sample data and the following query gives the correct result:

select max([created,d])[1] max_for_group_region 
 from default d
 group by jobGroup, region;

How does it work? It uses ‘group by’ to group documents by jobGroup and region, then creates a two-element array holding, for every document in the group:

  • the ‘created’ timestamp field
  • the document where the timestamp came from

It then applies the max function on the set of 2-element arrays. The max of a set of arrays looks for the maximum value in the first array position, and if there’s a tie look at the second position, and so on. In this case we are getting the two-element array with the max timestamp.

Now we have an array [ timestamp, document ], so we apply [1] to extract just the document.