<html><head><meta http-equiv="Content-Type" content="text/html charset=windows-1252"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div>Hi Bill,<br><div><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div><br></div><div>Is this what you mean:</div><div><br></div><div>SELECT voyageID FROM voyages WHERE v_date_d = (SELECT MAX(v_date_d) FROM voyages) AND v_shipID = 189 </div><div><br></div><div><br></div><div>HTH,</div><div><br></div><div><div>
<div style="letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><span class="Apple-style-span" style="border-collapse: separate; font-variant: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; border-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><span class="Apple-style-span" style="border-collapse: separate; font-variant: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; border-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="">Danny Lewkin</div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><font size="1">Cognosis NV</font></div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><font size="1">013/33 54 86</font></div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><font class="Apple-style-span" color="#ababab" size="1">Dorpsstraat 28</font></div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><font class="Apple-style-span" color="#ababab" size="1">3560 Lummen</font></div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><font class="Apple-style-span" color="#ababab" size="1"><br></font></div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><div style="margin: 0px; color: rgb(34, 34, 34); font-family: arial, sans-serif; font-size: 13px; background-color: rgb(255, 255, 255);"><b><i><u><span lang="EN-US" style="font-size: 10pt; font-family: 'Century Gothic', sans-serif; color: red;">Our office will be closed on:<u></u><u></u></span></u></i></b></div><div style="margin: 0px; color: rgb(34, 34, 34); font-family: arial, sans-serif; font-size: 13px; background-color: rgb(255, 255, 255);"><b><i><span style="font-size: 10pt; font-family: 'Century Gothic', sans-serif; color: red;">24 (afternoon) - 25 - 31/12/2013 & 01/01/2014</span></i></b></div></div><div style="font-family: Helvetica; font-weight: normal; font-style: normal;"><br></div></div></span><br class="Apple-interchange-newline"></div></span></div></span></div></div></div></div></div><blockquote type="cite"><div><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div><div><div style="letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><span class="Apple-style-span" style="border-collapse: separate; font-variant: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; border-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><span class="Apple-style-span" style="border-collapse: separate; font-variant: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; border-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;"></span><br class="Apple-interchange-newline"></div></span><br class="Apple-interchange-newline"></div><br class="Apple-interchange-newline"><br class="Apple-interchange-newline">
</div>
<br><div><div>On 03 Jan 2014, at 14:46, william humphrey <<a href="mailto:bill@bluewatermaritime.com">bill@bluewatermaritime.com</a>> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">Bart<br><br>SELECT voyageID FROM voyages WHERE MAX(v_date_d) AND v_shipID = 189<br><br>It doesn't like to mix with aggregate clause<br><br>On Thu, Jan 2, 2014 at 8:48 AM, william humphrey<br><<a href="mailto:bill@bluewatermaritime.com">bill@bluewatermaritime.com</a>> wrote:<br><blockquote type="cite">Bart<br><br>Baring in mind what Ruslan said about not using RecId and seeing the<br>method that Ivan showed me using ( ) I will try to do it with the<br>date. I will play around using vStudio and try to get it using the<br>max date from the records meeting the search less than the date of the<br>current record.<br><br>Bill<br><br>On Thu, Jan 2, 2014 at 8:00 AM, Bart Pietercil<br><<a href="mailto:bart.pietercil@cognosis.be">bart.pietercil@cognosis.be</a>> wrote:<br><blockquote type="cite">Multiple strategies come to mind to achieve what you are trying to do.<br><br>1) work with a creation_dt (timestamp default = npw)<br>when looking for the previous your where condition could determine the value<br>of the current creation_dt and take the max(creation_dt) from the records<br>where creation_dt < current_creation_dt<br>2) work with a sequence that you maintain by yourself (probably easiest with<br>triggers)<br><br>hth<br><br>bart<br>—<br>Our office will be closed on:<br>24 (afternoon) - 25 - 31/12/2013 & 01/01/2014<br><br>Bart Pietercil<br>Sent with aMac<br><br>On 2 Jan 2014 at 10:18:05, Ivan Smahin (<a href="mailto:ivan_smahin@paradigmasoft.com">ivan_smahin@paradigmasoft.com</a>)<br>wrote:<br><br><br>On Jan 1, 2014, at 10:28 PM, Jay Madren <<a href="mailto:jay@jaymadren.com">jay@jaymadren.com</a>> wrote:<br><br>Something like this should work:<br><br>SELECT voyageID FROM voyages WHERE RecID = (SELECT RecID - 1 FROM voyages<br>WHERE voyageID = 'S1301')<br><br><br> It is wrong because of potentially wholes in RecIDs.<br><br>Example:<br><br>Let we have an empty t1 table.<br><br>INSERT INTO t1 VALUES ...<br>INSERT INTO t1 VALUES ...<br>INSERT INTO t1 VALUES ...<br><br>SELECT RecID FROM t1;<br>--<br>1<br>2<br>3<br><br>Now,<br>DELETE FROM t1 WHERE RecID = 2;<br><br>SELECT RecID FROM t1;<br>--<br>1<br>3<br><br>RecID == 2 is marked as deleted record and you can not access it.<br>"Virtually" it does not exist:<br><br>SELECT RecID FROM t1 WHERE RecID = 2;<br>--<br><empty result><br><br><br>So you should not rely on RecID comparison.<br><br>The solution might be following:<br><br>SELECT voyuageID FROM voyuages WHERE RecID < (SELECT RecID FROM voyuages<br>WHERE voyuageID='S1301') ORDER BY RecID DESC LIMIT 1<br><br>But it looks ugly and works pretty slow.<br><br>I'm sure there is more elegant and proper solution if you describe exactly<br>why do you need to get the previous "physical" record.<br><br><br>--<br>Best regards,<br>Ivan Smahin<br>Senior Software Engineer<br>Paradigma Software, Inc<br>Valentina - The Ultra-Fast Database<br><a href="http://www.valentina-db.com/">http://www.valentina-db.com</a><br><br>_______________________________________________<br>Valentina mailing list<br><a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br>http://lists.macserve.net/mailman/listinfo/valentina<br><br><br>_______________________________________________<br>Valentina mailing list<br>Valentina@lists.macserve.net<br>http://lists.macserve.net/mailman/listinfo/valentina<br><br></blockquote><br><br><br>--<br><a href="http://www.bluewatermaritime.com/">http://www.bluewatermaritime.com</a><br></blockquote><br><br><br>-- <br><a href="http://www.bluewatermaritime.com/">http://www.bluewatermaritime.com</a><br>_______________________________________________<br>Valentina mailing list<br><a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br>http://lists.macserve.net/mailman/listinfo/valentina<br></blockquote></div><br></div></div></div></blockquote></div><br></body></html>