SQL help

[william humphrey]

Bart

Baring in mind what Ruslan said about not using RecId and seeing the
method that Ivan showed me using ( ) I will try to do it with the
date.  I will play around using vStudio and try to get it using the
max date from the records meeting the search less than the date of the
current record.

Bill

On Thu, Jan 2, 2014 at 8:00 AM, Bart Pietercil
<bart.pietercil at cognosis.be> wrote:
> 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
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>



-- 
http://www.bluewatermaritime.com