<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">No I don’t think that will work. But let’s do things properly.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Please setup your table and some example data here: www.sqlfiddle.com (never mind there is no valentina engine, take Postgresql or MySQL)</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Then explain in a mail containing the link to your fiddle the result you hope to get.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">This way we can work together in a more optimized way while understanding your problem better.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">bart (on holiday)</div> <div id="bloop_sign_1388759668171092992" class="bloop_sign"><span style="font-family:helvetica,arial;font-size:13px"></span>— <div><div apple-content-edited="true"><div style="font-family: Helvetica; font-size: medium; line-height: normal; orphans: 2; text-align: -webkit-auto; widows: 2; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="margin: 0px; color: rgb(34, 34, 34); font-family: arial, sans-serif; font-size: 13px;"><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><b><i><u><span lang="EN-US" xml:lang="EN-US" style="font-size: 10pt; font-family: 'Century Gothic', sans-serif; color: red;">Our office will be closed on:</span></u></i></b></span></div><div style="margin: 0px; color: rgb(34, 34, 34); font-family: arial, sans-serif; font-size: 13px;"><b><i><span style="font-size: 10pt; font-family: 'Century Gothic', sans-serif; color: red;">24 (afternoon) - <a href="tel://25 - 31/12/2013">25 - 31/12/2013</a> & <a href="http://airmail.calendar/2014-01-01%2012:00:00%20GMT+1">01/01/2014</a></span></i></b></div></div></div></div></div></div><div><br>Bart Pietercil<br><span>Sent with aMac</span></div></div> <br><p style="color:#A0A0A8;">On 3 Jan 2014 at 15:30:06, Danny Lewkin (<a href="mailto://danny.lewkin@cognosis.be">danny.lewkin@cognosis.be</a>) wrote:</p> <blockquote type="cite" class="clean_bq"><span><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div>
<title></title>
<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;">
<div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style=""><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;">Danny
Lewkin</span></div>
<div style="font-family: Helvetica; font-weight: normal; font-style: normal;">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><font size="1">Cognosis NV</font></span></div>
<div style="font-family: Helvetica; font-weight: normal; font-style: normal;">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><font size="1">013/33 54 86</font></span></div>
<div style="font-family: Helvetica; font-weight: normal; font-style: normal;">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><font class="Apple-style-span" color="#ABABAB" size="1">Dorpsstraat
28</font></span></div>
<div style="font-family: Helvetica; font-weight: normal; font-style: normal;">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><font class="Apple-style-span" color="#ABABAB" size="1">3560
Lummen</font></span></div>
<div style="font-family: Helvetica; font-weight: normal; font-style: normal;">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><font class="Apple-style-span" color="#ABABAB" size="1"><br></font></span></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);">
<span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><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:</span></u></i></b></span></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>
<br class="Apple-interchange-newline"></div>
</div>
</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;">
<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;">
<br class="Apple-interchange-newline"></span></div>
<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>
_______________________________________________
<br>Valentina mailing list
<br>Valentina@lists.macserve.net
<br>http://lists.macserve.net/mailman/listinfo/valentina
<br></div></div></span></blockquote></body></html>