SQL help

[Bart Pietercil]

Multiple strategies come to mind to achieve what you are trying to do.

1) work with a creation_dt (timestamp default = npw)
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
2) work with a sequence that you maintain by yourself (probably easiest with triggers)

hth

bart
— 
Our office will be closed on:
24 (afternoon) - 25 - 31/12/2013 & 01/01/2014

Bart Pietercil
Sent with aMac

On 2 Jan 2014 at 10:18:05, Ivan Smahin (ivan_smahin at paradigmasoft.com) wrote:


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

_______________________________________________  
Valentina mailing list  
Valentina at lists.macserve.net  
http://lists.macserve.net/mailman/listinfo/valentina  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20140102/a6bbbbd6/attachment-0001.html>