CODEWITHSUNDEEP
xquerymarklogic
Shalini
Shalini

Reputation: 348

How to get the maximum value of an element from entire database in Marklogic?

I want to get the Maximum value of <ID>from all the documents present inside the database.

Sample Document-

<root>
   <ID>3253523</ID>
   <value1>.....</value1>
   <value2>.....</value2>
   <value3>.....</value3>
   <value4>.....</value4>
    .....................
</root>

My database is having more than 1 million records and i want to fetch the ID which is having greatest value among all.

I can't use fn:last() because it won't give me the maximum value.

I need to use that value to create an INCREMENTAL COUNTER (The Maximum value will become my first value to the COUNTER).

Any Suggestions to fetch that value in an efficient way ? Because i can't do a cts:search() over 1 million records and then do orderby ascending and fetch the last value.

Upvotes: 0

Views: 346

Answers (2)

grtjn
grtjn

Reputation: 20414

When you need to get the max, or some other aggregate of an element containing date, price, number, or other kind of value, the answer from Elijah is adequate.

For the specific case of sequential numbers, there is a bit more to it. How to guarantee uniqueness across threads when you have parallel ingestion? It is a non-trivial problem, and we typically recommend against using sequential numbers for performance reasons because of that. Use random numbers instead. It makes collision practically impossible, and prevents contention to derive the max + 1 ID.

I've created a library that allows various ways of generating unique identifiers, and elaborates on the pros and cons of each:

https://github.com/grtjn/ml-unique#how-it-works

HTH!

Upvotes: 2

Elijah Bernstein-Cooper
Elijah Bernstein-Cooper

Reputation: 515

You could add an element range index to the ID element then use the cts:values function to retrieve the first of the indexed values in descending order.

Example:

(: assuming a path range index for an int scalar at path '/root/ID' :)
for $i in 1 to 100
  let $doc := <root><ID>{$i}</ID></root>
  return
    xdmp:document-insert("/test/doc-" || $i, $doc, (), "test");

(cts:values(cts:path-reference("/root/ID"), (), "descending"))[1]

Upvotes: 3

Related Questions