How to select record with max value?

Question

I have the following view :

function (doc, meta) {
  if (meta.type == "json")
  if ( doc.id 
      && doc.id.root.indexOf("1.2.250.1.199.16.1.1") == 0 
      && doc.setId
      && doc.versionNumber)
  {
    emit(doc.setId.extension, parseInt(doc.versionNumber.value))
  }
}

That returns the following result set :

Key Value
"4816f76e-33f9-456a-9e23-961799883994" 1
wound2QualificationRev1

"4816f76e-33f9-456a-9e23-961799883994" 2
wound2QualificationRev2

Now I would like to restrict the result set on the max value to return only

Key Value
"4816f76e-33f9-456a-9e23-961799883994" 2
wound2QualificationRev2

How would I modify the view ? With a custom reduce method ?

EDIT 1 : Please note that the result set shown here is partial. I can't use orderby and limit to achieve my goal.

@NinaScholz Well "emit()" is a Couchbase internal function. Did you see the question was related to Couchbase ? — , Jun 26 '15 at 13:01

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

I don't know Couchbase, but what about writing something like :

 emit(parseInt(doc.versionNumber.value),doc.setId.extension)

then querying it with :

?descending=true&&limit=1

You can also use a map reduce : How to calculate Max value using Map-Reduce in CouchDB?, or considerate the usage of N1QL, which implements MIN and MAX SQL queries

edited May 23 '17 at 10:26

Community

1
1

answered Jun 26 '15 at 13:25

Leogiciel

263
2
10

That would have been a solution until my first edit of the question. – Jun 26 '15 at 13:29
Sorry, I didn't see your edit. Then what about using a reduce : [link](http://stackoverflow.com/questions/10107693/how-to-calculate-max-value-using-map-reduce-in-couchdb) ? Other way should be the usage of N1QL, which implements MIN and MAX SQL queries. – Leogiciel Jun 26 '15 at 14:53
using a reduce is part of the solution. Thanks for that. – Jul 01 '15 at 15:52

score 0 · Answer 2 · edited May 23 '17 at 10:26

Well Kudos to @Leogiciel for the Max reduce function, I finally found the solution. First, the view definition is left unchanged. The solution is to use this implementation of the Max reduce function and, most importantly, group and reduce with query parameters.

The initial result set, shown here as raw Json:

{"total_rows":9,"rows":[
{"id":"wound1Image1","key":"0a41d087-1a7f-4d9f-8df9-83d0deb2566c","value":1},
{"id":"wound2QualificationRev1","key":"4816f76e-33f9-456a-9e23-961799883994","value":1},
{"id":"wound2QualificationRev2","key":"4816f76e-33f9-456a-9e23-961799883994","value":2},
{"id":"wound2Image3","key":"65ff979a-e1e0-43d4-a24f-a3ee0cc8b67c","value":1},
{"id":"wound2Image1","key":"69f29cf8-3ee7-4681-937d-d465edc9bb16","value":1},
{"id":"wound1Image2","key":"76ab9a67-a17b-4fc6-a105-60b57e971d4f","value":1},
{"id":"wound1Synthesis","key":"bc1a7ec0-30d7-4493-8dd9-dcf6d0b48cc9","value":1},
{"id":"wound1Qualification","key":"cb375a5f-e07d-449a-ab13-60dba16a0323","value":1},
{"id":"wound2Image2","key":"dfb5e7eb-6192-46ba-84d2-0e5996536930","value":1}
]
}

with the following query parameters:

?group=true&reduce=true

returns the correct result set:

{"rows":[
{"key":"0a41d087-1a7f-4d9f-8df9-83d0deb2566c","value":1},
{"key":"4816f76e-33f9-456a-9e23-961799883994","value":2},
{"key":"65ff979a-e1e0-43d4-a24f-a3ee0cc8b67c","value":1},
{"key":"69f29cf8-3ee7-4681-937d-d465edc9bb16","value":1},
{"key":"76ab9a67-a17b-4fc6-a105-60b57e971d4f","value":1},
{"key":"bc1a7ec0-30d7-4493-8dd9-dcf6d0b48cc9","value":1},

{"key":"cb375a5f-e07d-449a-ab13-60dba16a0323","value":1},    {"key":"dfb5e7eb-6192-46ba-84d2-0e5996536930","value":1}
]
}

How to select record with max value?

2 Answers2