N1Q1 get documents where field is of the highest value when group by 2 fields

SQL++
1

I have a series of documents in the bucket in which I will like to group by by fields, kvp.internal and kvp.external and return only the document with highest value in metadata.published among the grouped fields.

  • You can assume that published is always unique and incremental.

E.g. I would like to retrieve the following results from the bucket as described below.

Results

{
“kvp”: {
“internal”: “dog”,
“external”: “dog”
},
“metadata”: {
“published”: 1470648328253,
…
},
…
}
{
“kvp”: {
“internal”: “cat”,
“external”: “cat”
},
“metadata”: {
“published”: 1470648328253,
…
},
…
}

====================================================
Bucket

{
“kvp”: {
“internal”: “dog”,
“external”: “dog”
},
“metadata”: {
“published”: 1470648328253,
…
},
…
}
{
“kvp”: {
“internal”: “dog”,
“external”: “dog”
},
“metadata”: {
“published”: 1470648320000,
…
},
…
}
{
“kvp”: {
“internal”: “cat”,
“external”: “cat”
},
“metadata”: {
“published”: 1470648300000,
…
},
…
}
{
“kvp”: {
“internal”: “cat”,
“external”: “cat”
},
“metadata”: {
“published”: 1470648328253,
…
},
…
}

2

Here is the query. If you have a WHERE clause, you should add a corresponding index.

SELECT MAX( [ published, doc ] )[1] AS d
FROM mybucket doc
GROUP BY kvp.internal, kvp.external;
3

so I slightly adjusted the query

  1. to match the name of my repository.
  2. I assume it’s 0 not 1

SELECT MAX( [metadata.published, doc])[0] AS d
FROM zeusresource
GROUP BY kvp.internal, kvp.external;

This only returns me the max value of field:published but does not return me the document

4

Why did you change it from 0 to 1.

5

If I were to keep it as 1, it returns no result where changing it to 0 returns the max value of published. I guess the square bracket refers to the index in the array where max is evaluated. Clearly I can be wrong here as I am quite new to n1ql.

SELECT MAX( [metadata.published, doc])[1] AS d
FROM zeusresource
GROUP BY kvp.internal, kvp.external;

6

Ok my bad, I think I understood your reply.

SELECT MAX([metadata.published, zeusresource])[1] AS d
FROM zeusresource
GROUP BY kvp.internal, kvp.external;

I suspect this is how it should be,the Max will identify the document with the max value of metadata.published and return the actual document.

Feel free to correct me.

1 Like
7

Your query looks good now.

8

Thank you for your quick reply. cheers

1 Like
9

Hi,
I have Sample bucket, with multiple documents having an attribute lineaItemID. Its values are integer ( like 1,2,3,4,…).
Now, want to take maximum lineItmeID from this bucket using below query:
select max(u.lineItemID ) from Sample u where meta(u).id like “For Profit:%”;

But, the result is not coming properly. Could you please let me know whether the query is correct or not. If not , the please share the query.

10

It should work. Could you please more specific.What is not working.

11

Moumita, your query looks correct.

To help debug the problem, could you give us some sample data (maybe 5 simplified documents) that shows the problem? Also, what version are you running?

12

Thanks a lot for your help. The query is now giving proper max value.
I have to add another condition required for my project .

13

You can add what ever condition you want. But follow SQL syntax and semantics