<html><head><meta http-equiv="Content-Type" content="text/html charset=iso-8859-1"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><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></body></html>