This is one solution, using "empty greatest" (missing dates will be returned first in the ordered results):
for $job in //job order by $job/StartDate/text()/xs:date( replace(., "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2") ) descending empty greatest count $position where $position eq 1 return $job The following one may be more intuitive: for $job in //job let $startDate := $job/StartDate/text() order by ( if($startDate) then ( xs:date(replace($startDate, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")) ) else () ) descending empty greatest count $position where $position eq 1 return $job Cheers, Christian ______________________________________ On Sun, Nov 22, 2015 at 1:42 AM, sudheshna iyer <sudheshnai...@yahoo.com> wrote: > Currently below works for me: > > for $job in //job > order by xs:date(replace($job/StartDate, "(\d{2})-(\d{2})-(\d{4})", > "$3-$1-$2")) descending > count $position > where $position eq 1 > return $job > > To this, I want to add: >> Just need one more modification to this. In the input if the StartDate is >> blank, then I should consider that as the current job and return the job >> that has blank as the output. > > > Thank you for your response. > > ________________________________ > From: Christian Grün <christian.gr...@gmail.com> > To: sudheshna iyer <sudheshnai...@yahoo.com> > Cc: "talk@x-query.com" <talk@x-query.com> > Sent: Sunday, November 15, 2015 4:35 AM > > Subject: Re: [xquery-talk] Xquery : Sort and get only the first record > > How does your current XQuery expression look like? > > > On Sun, Nov 15, 2015 at 9:11 AM, sudheshna iyer <sudheshnai...@yahoo.com> > wrote: >> Thank you all for the reply. >> >> Just need one more modification to this. In the input if the StartDate is >> blank, then I should consider that as the current job and return the job >> that has blank as the output. >> >> If there are no jobs which have startDate as blank, then it should return >> the recent startdate job (for which we already have the response as shown >> in >> email chain,,) >> >> For eg, in the below case, response should be the job element with id = 1: >> <?xml version="1.0" encoding="UTF-8"?> >> <Request> >> <SessionInfo> >> <uid>qq</uid> >> <pwd>qq</pwd> >> </SessionInfo> >> <Param> >> <CustomerInfo> >> <Contact> >> <Number>123</Number> >> <Name>aaa bbb</Name> >> <Jobs> >> <job> >> <Name>Analyst</Name> >> <Id>1</Id> >> <StartDate></StartDate> >> <EndDate>1</EndDate> >> </job> >> <job> >> <Name>Programmer</Name> >> <Id>2</Id> >> <StartDate>08-31-2015</StartDate> >> <EndDate>2</EndDate> >> </job> >> </Jobs> >> </Contact> >> </CustomerInfo> >> </Param> >> </Request> >> >> >> >> >> ________________________________ >> From: Ghislain Fourny <g...@28.io> >> To: Ronald Bourret <rpbour...@rpbourret.com> >> Cc: "talk@x-query.com" <talk@x-query.com>; sudheshna iyer >> <sudheshnai...@yahoo.com> >> Sent: Tuesday, November 3, 2015 4:51 AM >> Subject: Re: [xquery-talk] Xquery : Sort and get only the first record >> >> Hi Ronald, >> >> You can also use a count clause if you have XQuery 3.0! It makes it easier >> to read. Like so: >> >> for $job in doc('x.xml')//job >> order by xs:date(replace($job/StartDate, "(\d{2})-(\d{2})-(\d{4})", >> "$3-$1-$2")) descending >> count $position >> where $position eq 1 >> return $job >> >> I hope it helps. >> >> Kind regards, >> Ghislain >> >> >> >> >> On Mon, Nov 2, 2015 at 6:27 PM, Ronald Bourret <rpbour...@rpbourret.com> >> wrote: >> >> Can't use just use a predicate that returns the first element in the >> sorted >> sequence? Something like: >> >> let $seq = for $job in doc('x.xml')//job >> order by >> xs:date(replace($job/StartDate, >> "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2") >> ) descending >> return $job >> return $seq[1] >> >> (My apologies if the syntax isn't exact. I haven't written an XQuery for >> quite a while.) >> >> -- Ron >> >> On 11/2/2015 5:53 AM, sudheshna iyer wrote: >> >> Thank you very much, Christian. Let me try this.. >> >> >> >> ------------------------------------------------------------------------ >> *From:* Christian Grün <christian.gr...@gmail.com> >> *To:* sudheshna iyer <sudheshnai...@yahoo.com> >> *Cc:* "talk@x-query.com" <talk@x-query.com> >> *Sent:* Sunday, November 1, 2015 4:26 PM >> *Subject:* Re: [xquery-talk] Xquery : Sort and get only the first record >> >> If your XQuery processor supports XQuery Update, this would be one >> solution: >> >> copy $xml := doc('x.xml') >> modify ( >> delete node subsequence( >> for $job in $xml//job >> order by xs:date(replace($job/StartDate, >> "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2") >> ) descending >> return $job >> , 2) >> ) >> return $xml >> >> Best, >> Christian >> >> >> >> >> On Sun, Nov 1, 2015 at 8:51 PM, sudheshna iyer <sudheshnai...@yahoo.com >> <mailto:sudheshnai...@yahoo.com>> wrote: >> > Team, >> > >> > I have an xml which has multiple jobs elements. I want to sort the >> jobs and >> > want to output only the latest job. Note that <EndDate> can be empty >> > indicating that it is the current job. >> > >> > Basically I want to order by jobs/job/EndDate in descending fashion and >> > select only the first record.. >> > >> > How do I do that using xquery? >> > >> > Input request: >> > <?xml version="1.0" encoding="UTF-8"?> >> > <Request> >> > <SessionInfo> >> > <uid>qq</uid> >> > <pwd>qq</pwd> >> > </SessionInfo> >> > <Param> >> > <CustomerInfo> >> > <Contact> >> > <Number>123</Number> >> > <Name>aaa bbb</Name> >> > <Jobs> >> > <job> >> > <Name>Analyst</Name> >> > <Id>1</Id> >> > <StartDate>01-01-2015</StartDate> >> > <EndDate>08-30-2015</EndDate> >> > </job> >> > <job> >> > <Name>Programmer</Name> >> > <Id>2</Id> >> > <StartDate>08-31-2015</StartDate> >> > <EndDate /> >> > </job> >> > </Jobs> >> > </Contact> >> > </CustomerInfo> >> > </Param> >> > </Request> >> > >> > Expected output: >> > >> > <?xml version="1.0" encoding="UTF-8"?> >> > <Request> >> > <SessionInfo> >> > <uid>qq</uid> >> > <pwd>qq</pwd> >> > </SessionInfo> >> > <Param> >> > <CustomerInfo> >> > <Contact> >> > <Number>123</Number> >> > <Name>aaa bbb</Name> >> > <Jobs> >> > <job> >> > <Name>Programmer</Name> >> > <Id>2</Id> >> > <StartDate>08-31-2015</StartDate> >> > <EndDate/> >> > </job> >> > </Jobs> >> > </Contact> >> > </CustomerInfo> >> > </Param> >> > </Request> >> > >> > Your help is greatly appreciated. >> >> > >> > >> > >> > >> > _______________________________________________ >> > talk@x-query.com <mailto:talk@x-query.com> >> > http://x-query.com/mailman/listinfo/talk >> >> >> >> >> >> _______________________________________________ >> talk@x-query.com >> http://x-query.com/mailman/listinfo/talk >> >> >> --- >> This email has been checked for viruses by Avast antivirus software. >> https://www.avast.com/antivirus > > > >> >> _______________________________________________ >> talk@x-query.com >> http://x-query.com/mailman/listinfo/talk >> >> >> >> _______________________________________________ >> talk@x-query.com >> http://x-query.com/mailman/listinfo/talk >> >> >> >> _______________________________________________ >> talk@x-query.com >> http://x-query.com/mailman/listinfo/talk > > _______________________________________________ talk@x-query.com http://x-query.com/mailman/listinfo/talk