<div dir="ltr"><span style="font-size:12.8px">Hi Ronald,</span><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">You can also use a count clause if you have XQuery 3.0! It makes it easier to read. Like so:</span></div><div><span style="font-size:12.8px"><br></span></div>for $job in doc('x.xml')//job<br>order by xs:date(replace($job/StartDate, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")) descending<br>count $position<br>where $position eq 1<br>return $job<div><br></div><div>I hope it helps.<br><div><br></div><div>Kind regards,</div><div>Ghislain</div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Nov 2, 2015 at 6:27 PM, Ronald Bourret <span dir="ltr"><<a href="mailto:rpbourret@rpbourret.com" target="_blank">rpbourret@rpbourret.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Can't use just use a predicate that returns the first element in the sorted sequence? Something like:<br>
<br>
let $seq = for $job in doc('x.xml')//job<span class=""><br>
order by<br>
xs:date(replace($job/StartDate,<br>
"(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")<br>
) descending<br>
return $job<br></span>
return $seq[1]<br>
<br>
(My apologies if the syntax isn't exact. I haven't written an XQuery for quite a while.)<br>
<br>
-- Ron<br>
<br>
On 11/2/2015 5:53 AM, sudheshna iyer wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thank you very much, Christian. Let me try this..<br>
<br>
<br>
<br>
------------------------------------------------------------------------<br>
*From:* Christian Grün <<a href="mailto:christian.gruen@gmail.com" target="_blank">christian.gruen@gmail.com</a>><br>
*To:* sudheshna iyer <<a href="mailto:sudheshnaiyer@yahoo.com" target="_blank">sudheshnaiyer@yahoo.com</a>><br>
*Cc:* "<a href="mailto:talk@x-query.com" target="_blank">talk@x-query.com</a>" <<a href="mailto:talk@x-query.com" target="_blank">talk@x-query.com</a>><br>
*Sent:* Sunday, November 1, 2015 4:26 PM<br>
*Subject:* Re: [xquery-talk] Xquery : Sort and get only the first record<span class=""><br>
<br>
If your XQuery processor supports XQuery Update, this would be one solution:<br>
<br>
copy $xml := doc('x.xml')<br>
modify (<br>
delete node subsequence(<br>
for $job in $xml//job<br>
order by xs:date(replace($job/StartDate,<br>
"(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")<br>
) descending<br>
return $job<br>
, 2)<br>
)<br>
return $xml<br>
<br>
Best,<br>
Christian<br>
<br>
<br>
<br>
<br>
On Sun, Nov 1, 2015 at 8:51 PM, sudheshna iyer <<a href="mailto:sudheshnaiyer@yahoo.com" target="_blank">sudheshnaiyer@yahoo.com</a><br></span><div><div class="h5">
<mailto:<a href="mailto:sudheshnaiyer@yahoo.com" target="_blank">sudheshnaiyer@yahoo.com</a>>> wrote:<br>
> Team,<br>
><br>
> I have an xml which has multiple jobs elements. I want to sort the<br>
jobs and<br>
> want to output only the latest job. Note that <EndDate> can be empty<br>
> indicating that it is the current job.<br>
><br>
> Basically I want to order by jobs/job/EndDate in descending fashion and<br>
> select only the first record..<br>
><br>
> How do I do that using xquery?<br>
><br>
> Input request:<br>
> <?xml version="1.0" encoding="UTF-8"?><br>
> <Request><br>
> <SessionInfo><br>
> <uid>qq</uid><br>
> <pwd>qq</pwd><br>
> </SessionInfo><br>
> <Param><br>
> <CustomerInfo><br>
> <Contact><br>
> <Number>123</Number><br>
> <Name>aaa bbb</Name><br>
> <Jobs><br>
> <job><br>
> <Name>Analyst</Name><br>
> <Id>1</Id><br>
> <StartDate>01-01-2015</StartDate><br>
> <EndDate>08-30-2015</EndDate><br>
> </job><br>
> <job><br>
> <Name>Programmer</Name><br>
> <Id>2</Id><br>
> <StartDate>08-31-2015</StartDate><br>
> <EndDate /><br>
> </job><br>
> </Jobs><br>
> </Contact><br>
> </CustomerInfo><br>
> </Param><br>
> </Request><br>
><br>
> Expected output:<br>
><br>
> <?xml version="1.0" encoding="UTF-8"?><br>
> <Request><br>
> <SessionInfo><br>
> <uid>qq</uid><br>
> <pwd>qq</pwd><br>
> </SessionInfo><br>
> <Param><br>
> <CustomerInfo><br>
> <Contact><br>
> <Number>123</Number><br>
> <Name>aaa bbb</Name><br>
> <Jobs><br>
> <job><br>
> <Name>Programmer</Name><br>
> <Id>2</Id><br>
> <StartDate>08-31-2015</StartDate><br>
> <EndDate/><br>
> </job><br>
> </Jobs><br>
> </Contact><br>
> </CustomerInfo><br>
> </Param><br>
> </Request><br>
><br>
> Your help is greatly appreciated.<br>
<br>
><br>
><br>
><br>
><br>
> _______________________________________________<br></div></div>
> <a href="mailto:talk@x-query.com" target="_blank">talk@x-query.com</a> <mailto:<a href="mailto:talk@x-query.com" target="_blank">talk@x-query.com</a>><span class=""><br>
> <a href="http://x-query.com/mailman/listinfo/talk" rel="noreferrer" target="_blank">http://x-query.com/mailman/listinfo/talk</a><br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________<br>
<a href="mailto:talk@x-query.com" target="_blank">talk@x-query.com</a><br>
<a href="http://x-query.com/mailman/listinfo/talk" rel="noreferrer" target="_blank">http://x-query.com/mailman/listinfo/talk</a><br>
<br>
</span></blockquote>
<br>
---<br>
This email has been checked for viruses by Avast antivirus software.<br>
<a href="https://www.avast.com/antivirus" rel="noreferrer" target="_blank">https://www.avast.com/antivirus</a><br>
<br>
_______________________________________________<br>
<a href="mailto:talk@x-query.com" target="_blank">talk@x-query.com</a><br>
<a href="http://x-query.com/mailman/listinfo/talk" rel="noreferrer" target="_blank">http://x-query.com/mailman/listinfo/talk</a><br>
</blockquote></div><br></div>