Skip to content

Latest commit

 

History

History
90 lines (65 loc) · 4.03 KB

aggregate-functions-min.md

File metadata and controls

90 lines (65 loc) · 4.03 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords dev_langs
min Function (XQuery)
Learn about the XQuery min() function that returns the one item in a sequence whose value is less that that of all the others.
rothja
jroth
03/09/2017
sql
xml
language-reference
fn:min function
min function [XQuery]
XML

Aggregate Functions - min

[!INCLUDE SQL Server Azure SQL Database]

Returns from a sequence of atomic values, $arg, the one item whose value is less than that of all the others.

Syntax

  
fn:min($arg as xdt:anyAtomicType*) as xdt:anyAtomicType?

Arguments

$arg
Sequence of items from which to return the minimum value.

Remarks

All types of the atomized values that are passed to min() have to be subtypes of the same base type. Base types that are accepted are the types that support the gt operation. These types include the three built-in numeric base types, the date/time base types, xs:string, xs:boolean, and xdt:untypedAtomic. Values of type xdt:untypedAtomic are cast to xs:double. If there is a mixture of these types, or if other values of other types are passed, a static error is raised.

The result of min() receives the base type of the passed in types, such as xs:double in the case of xdt:untypedAtomic. If the input is statically empty, empty is implied and a static error is returned.

The min() function returns the one value in the sequence that is smaller than any other in the input sequence. For xs:string values, the default Unicode Codepoint Collation is being used. If an xdt:untypedAtomic value cannot be cast to xs:double, the value is ignored in the input sequence, $arg. If the input is a dynamically calculated empty sequence, the empty sequence is returned.

Examples

This topic provides XQuery examples against XML instances that are stored in various xml type columns in the AdventureWorks database.

A. Using the min() XQuery function to find the work center location that has the fewest labor hours

The following query retrieves all work center locations in the manufacturing process of the product model (ProductModelID=7) that have the fewest labor hours. Generally, as shown in the following, a single location is returned. If multiple locations had an equal number of minimum labor hours, they would all be returned.

select ProductModelID, Name, Instructions.query('  
  declare namespace AWMI=  
    "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
  for   $Location in /AWMI:root/AWMI:Location  
  where $Location/@LaborHours =  
          min( /AWMI:root/AWMI:Location/@LaborHours )  
return  
  <Location WCID=     "{ $Location/@LocationID }"   
              LaborHrs= "{ $Location/@LaborHours }" />  
  ') as Result   
FROM  Production.ProductModel  
WHERE ProductModelID=7

Note the following from the previous query:

  • The namespace keyword in the XQuery prolog defines a namespace prefix. This prefix is then used in the XQuery body.

The XQuery body constructs the XML that has a <Location> element with WCID and LaborHrs attributes.

  • The query also retrieves the ProductModelID and name values.

This is the result:

ProductModelID   Name              Result  
---------------  ----------------  ---------------------------------  
7                HL Touring Frame  <Location WCID="45" LaborHrs="0.5"/>

Implementation Limitations

These are the limitations:

  • The min() function maps all integers to xs:decimal.

  • The min() function on values of type xs:duration is not supported.

  • Sequences that mix types across base type boundaries are not supported.

  • The syntactic option that provides a collation is not supported.

See Also

XQuery Functions against the xml Data Type