<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;">Multiple strategies come to mind to achieve what you are trying to do.</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;">1) work with a creation_dt (timestamp default = npw)</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;">when looking for the previous your where condition could determine the value of the current creation_dt and take the max(creation_dt) from the records where creation_dt < current_creation_dt</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;">2) work with a sequence that you maintain by yourself (probably easiest with triggers)</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;">hth</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</div> <div id="bloop_sign_1388663791225084160" 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 2 Jan 2014 at 10:18:05, Ivan Smahin (<a href="mailto://ivan_smahin@paradigmasoft.com">ivan_smahin@paradigmasoft.com</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>
<br>
<div>
<div>On Jan 1, 2014, at 10:28 PM, Jay Madren <<a href="mailto:jay@jaymadren.com">jay@jaymadren.com</a>> wrote:</div>
<br class="Apple-interchange-newline">
<blockquote type="cite">
<div dir="ltr">
<div class="gmail_default" style="font-family:tahoma,sans-serif;font-size:small">Something like this
should work:</div>
<div class="gmail_default" style="font-family:tahoma,sans-serif;font-size:small"><br></div>
<div class="gmail_default" style="font-family:tahoma,sans-serif;font-size:small"><span style="font-family:arial,sans-serif;font-size:16.363636016845703px">SELECT
voyageID FROM voyages WHERE RecID = (SELECT RecID - 1 FROM voyages
WHERE voyageID = 'S1301')</span><br></div>
</div>
</blockquote>
</div>
<div><br></div>
It is wrong because of potentially wholes in RecIDs.
<div><br></div>
<div>Example:</div>
<div><br></div>
<div>Let we have an empty t1 table.</div>
<div><br></div>
<div>INSERT INTO t1 VALUES ...</div>
<div>INSERT INTO t1 VALUES ...</div>
<div>
<div>
<div>INSERT INTO t1 VALUES ...</div>
</div>
<div><br></div>
<div>SELECT RecID FROM t1;</div>
<div>--</div>
<div>1</div>
<div>2</div>
<div>3</div>
<div><br></div>
<div>Now,</div>
<div>DELETE FROM t1 WHERE RecID = 2;</div>
<div><br></div>
<div>
<div>SELECT RecID FROM t1;</div>
<div>--</div>
</div>
<div>1</div>
<div>3</div>
<div><br></div>
<div>RecID == 2 is marked as deleted record and you can not access
it. "Virtually" it does not exist:</div>
<div><br></div>
<div>SELECT RecID FROM t1 WHERE RecID = 2;</div>
<div>--</div>
<div><empty result></div>
<div><br></div>
<div><br></div>
<div>So you should not rely on RecID comparison.</div>
<div><br></div>
<div>The solution might be following:</div>
<div><br></div>
<div>SELECT voyuageID FROM voyuages WHERE RecID < (SELECT RecID
FROM voyuages WHERE voyuageID='S1301') ORDER BY RecID DESC LIMIT
1</div>
<div><br></div>
<div>But it looks ugly and works pretty slow.</div>
<div><br></div>
<div>I'm sure there is more elegant and proper solution if you
describe exactly why do you need to get the previous "physical"
record.</div>
<div><br></div>
<div><br></div>
<div apple-content-edited="true"><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;">-- <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></span></div>
</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>