Sorting Data

Example 5. Order By Pushdown

The XQuery uses the Order By clause to sort database data in the database.

XQuery

for $u in collection('USERS')/USERS
order by $u/LASTNAME
return <user name="{$u/LASTNAME}"/>

Generated SQL

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  nrm5."LASTNAME" ASC

Example 6. Order By Pushdown - empty least

DataDirect XQuery supports all variants of Order By in XQuery, pushing them down to the database. For instance, here is the SQL generated for a query that uses the empty least clause.

XQuery

for $u in collection('USERS')/USERS
order by $u/OTHERNAME descending empty least
return <user name="{$u/LASTNAME}"/>

Generated SQL (Oracle 10gR2)

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  nrm5."OTHERNAME" DESC NULLS LAST

Example 7. Sorting empty greatest on Microsoft SQL Server

In some cases, supporting an XQuery sort order requires some ingenuity. For instance, Microsoft SQL Server does not support sorting NULL high, so we use a simple trick to implement empty greatest for this database efficiently, while still correctly implementing the semantics of XQuery.

XQuery

for $u in collection('USERS')/USERS
order by $u/OTHERNAME descending empty greatest
return <user name="{$u/LASTNAME}"/>

Generated SQL (SQL Server)

SELECT ALL
  nrm5."LASTNAME" AS RACOL1
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  (CASE WHEN nrm5."OTHERNAME" IS NULL THEN 0 ELSE 1 END) ASC,
  nrm5."OTHERNAME" DESC