SQL help

[Ivan Smahin]

On Jan 1, 2014, at 10:28 PM, Jay Madren <jay at jaymadren.com> wrote:

> Something like this should work:
> 
> SELECT voyageID FROM voyages WHERE RecID = (SELECT RecID - 1 FROM voyages WHERE voyageID = 'S1301')


 It is wrong because of potentially wholes in RecIDs.

Example:

Let we have an empty t1 table.

INSERT INTO t1 VALUES ...
INSERT INTO t1 VALUES ...
INSERT INTO t1 VALUES ...

SELECT RecID FROM t1;
--
1
2
3

Now,
DELETE FROM t1 WHERE RecID = 2;

SELECT RecID FROM t1;
--
1
3

RecID == 2 is marked as deleted record and you can not access it. "Virtually" it does not exist:

SELECT RecID FROM t1 WHERE RecID = 2;
--
<empty result>


So you should not rely on RecID comparison.

The solution might be following:

SELECT voyuageID FROM voyuages WHERE RecID < (SELECT RecID FROM voyuages WHERE voyuageID='S1301') ORDER BY RecID DESC LIMIT 1

But it looks ugly and works pretty slow.

I'm sure there is more elegant and proper solution if you describe exactly why do you need to get the previous "physical" record.


-- 
Best regards,
Ivan Smahin
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20140102/759c44f8/attachment.html>