Fwd: SQL help

[william humphrey]

I've been thinking that a sequence maintained with a trigger might be
very useful for other things too. I never thought of numbering
sequentially the voyages of each vessel. It solves some other
problems. Right now voyage numbers increment from zero at the start of
each new year (which is what caused the problem) but there is no
reason why I can't have an extra field which has every voyage for that
vessel down through the years.

On Fri, Jan 3, 2014 at 10:41 AM, william humphrey
<bill at bluewatermaritime.com> wrote:
> Bart
>
> That's an amazing resource.
>
> Bill
>
> On Fri, Jan 3, 2014 at 10:38 AM, Bart Pietercil
> <bart.pietercil at cognosis.be> wrote:
>> No I don’t think that will work. But let’s do things properly.
>> Please setup your table and some example data here: www.sqlfiddle.com (never
>> mind there is no valentina engine, take Postgresql or MySQL)
>> Then explain in a mail containing the link to your fiddle the result you
>> hope to get.
>> This way we can work together in a more optimized way while understanding
>> your problem better.
>>
>> bart (on holiday)
>> —
>> Our office will be closed on:
>> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014
>>
>> Bart Pietercil
>> Sent with aMac
>>
>> On 3 Jan 2014 at 15:30:06, Danny Lewkin (danny.lewkin at cognosis.be) wrote:
>>
>> Hi Bill,
>>
>> Is this what you mean:
>>
>> SELECT voyageID FROM voyages WHERE v_date_d = (SELECT MAX(v_date_d) FROM
>> voyages) AND v_shipID = 189
>>
>>
>> HTH,
>>
>> Danny Lewkin
>> Cognosis NV
>> 013/33 54 86
>> Dorpsstraat 28
>> 3560 Lummen
>>
>> Our office will be closed on:
>> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014
>>
>>
>>
>>
>>
>>
>>
>> On 03 Jan 2014, at 14:46, william humphrey <bill at bluewatermaritime.com>
>> wrote:
>>
>> Bart
>>
>> SELECT voyageID FROM voyages WHERE MAX(v_date_d) AND  v_shipID = 189
>>
>> It doesn't like to mix with aggregate clause
>>
>> On Thu, Jan 2, 2014 at 8:48 AM, william humphrey
>> <bill at bluewatermaritime.com> wrote:
>>
>> 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
>>
>>
>>
>>
>> --
>> http://www.bluewatermaritime.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
>>
>>
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>
>
>
> --
> http://www.bluewatermaritime.com



-- 
http://www.bluewatermaritime.com