<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Seems like an entertaining riddle.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">I think a view could be an option. Or maybe a simple query will do.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"> If possible can you sent me offlist a database with (a sub) set of data and a concrete question. Or write an sqlfiddle</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">I think I could have a look at it sunday .</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">I wrote some more comments inline your answer below</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">my mail: bart(at)m-3(dot)be</div> <div id="bloop_sign_1388791202172086016" class="bloop_sign"><span style="font-family:helvetica,arial;font-size:13px"></span>— <div><div apple-content-edited="true"><div style="font-family: Helvetica; font-size: medium; line-height: normal; orphans: 2; text-align: -webkit-auto; widows: 2; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div style="margin: 0px; color: rgb(34, 34, 34); font-family: arial, sans-serif; font-size: 13px;"><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><b><i><u><span lang="EN-US" xml:lang="EN-US" style="font-size: 10pt; font-family: 'Century Gothic', sans-serif; color: red;">Our office will be closed on:</span></u></i></b></span></div><div style="margin: 0px; color: rgb(34, 34, 34); font-family: arial, sans-serif; font-size: 13px;"><b><i><span style="font-size: 10pt; font-family: 'Century Gothic', sans-serif; color: red;">24 (afternoon) - <a href="tel://25 - 31/12/2013">25 - 31/12/2013</a> & <a href="http://airmail.calendar/2014-01-01%2012:00:00%20GMT+1">01/01/2014</a></span></i></b></div></div></div></div></div></div><div><br>Bart Pietercil<br><span>Sent with aMac</span></div></div> <br><p style="color:#A0A0A8;">On 4 Jan 2014 at 00:14:58, william humphrey (<a href="mailto://bill@bluewatermaritime.com">bill@bluewatermaritime.com</a>) wrote:</p> <div><blockquote type="cite" class="clean_bq" style="font-family: Helvetica, Arial; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);"><span><div><div>The problem is that the voyage ID I am using is the one for U.S.<span class="Apple-converted-space"> </span><br>customs which is a one letter code for vessel (even though in the<span class="Apple-converted-space"> </span><br>database each vessel has a number ID) and then two digits for year and<span class="Apple-converted-space"> </span><br>then two digits for the sequence of the vessel arrival for that year.<span class="Apple-converted-space"> </span><br><br>For example H1401 is the first arrival for vessel "H" actually that<span class="Apple-converted-space"> </span><br>ship the Midnight Coast is number 138 in the vessel table. It turns<span class="Apple-converted-space"> </span><br>out very sub-optimal. I should have had it be vessel number 138-033<span class="Apple-converted-space"> </span><br>and so on for ever single voyage of that vessel for my ID field in the<span class="Apple-converted-space"> </span><br>voyages and not use the voyage ID required by Customs (even though it<span class="Apple-converted-space"> </span><br>is unique) as it's difficult to extract from H1401 that it is the 33nd<span class="Apple-converted-space"> </span><br>voyage of vessel number 138. So now the best thing to do would be make<span class="Apple-converted-space"> </span><br>an auto-increment field that is a combination of vessel ID and<span class="Apple-converted-space"> </span><br>sequence of that vessel.<span class="Apple-converted-space"> </span><br><br>This kind of sub-optimal database design happens sometimes. So the<span class="Apple-converted-space"> </span><br>problem I'm having is easily asking for the voyage immediately<span class="Apple-converted-space"> </span><br>preceding a particular voyage of a particular vessel. It isn't easy to<span class="Apple-converted-space"> </span><br>extract that using the voyage ID as that changes increment each year.<span class="Apple-converted-space"> </span><br>It also is a different format depending on whether it's a passenger<span class="Apple-converted-space"> </span><br>vessel or a cargo vessel (passenger vessel has a two-letter code).<span class="Apple-converted-space"> </span></div></div></span></blockquote></div><p>So the FOUR LAST characters determine the year and vessel arrival. This can be easily extracted using string functions (look at right and length) so you could make seqnr and year method fields.</p><p>Cast the calculations to numeric output. So now you can order by those fields (do not forget to index)</p><p>If your seqnr > 1 then you could look for seqnr -1 when seqnr = 1 then look for year -1 and max(seqnr)</p><p>ex H1401 -> year = 14 and seq = 1</p><p>so we need to look for year = 14-1 and seq = max(seq) where vessel = ‘H’ and year = 13</p><p><br></p><p>right?</p><p><br></p><p>off to sleep now, as said tomorrow busy day, I’ll look at your answer sunday ( now + 36h)</p><p><br></p><p><br></p><div><blockquote type="cite" class="clean_bq" style="font-family: Helvetica, Arial; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);"><span><div><div><br><br>Bill<span class="Apple-converted-space"> </span><br><br>On Fri, Jan 3, 2014 at 7:07 PM, Bart Pietercil<span class="Apple-converted-space"> </span><br><bart.pietercil@cognosis.be> wrote:<span class="Apple-converted-space"> </span><br>> Hmm, it seems to me that your db schema is suboptimal here but without<span class="Apple-converted-space"> </span><br>> knowing the schema this remains of course guessing.<span class="Apple-converted-space"> </span><br>> I would expect your schema to be something like this<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> tbl_voyages<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> voyage_id,<span class="Apple-converted-space"> </span><br>> fk_vessel_id,<span class="Apple-converted-space"> </span><br>> from,<span class="Apple-converted-space"> </span><br>> to,<span class="Apple-converted-space"> </span><br>> departure,<span class="Apple-converted-space"> </span><br>> arrival,<span class="Apple-converted-space"> </span><br>> ….<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> tbl_vessels<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> vessel_id,<span class="Apple-converted-space"> </span><br>> vessel_name,<span class="Apple-converted-space"> </span><br>> vessel_capacity,<span class="Apple-converted-space"> </span><br>> …<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> both voyage_id and vessel_id are sequences.<span class="Apple-converted-space"> </span><br>> fk_vessel_id is the foreign key holding the vessel_id for the vessel that is<span class="Apple-converted-space"> </span><br>> doing the voyage (voyage_id).<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> If this is your schema then please elaborate on the problem your having.<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> regards<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> bart<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> —<span class="Apple-converted-space"> </span><br>> Our office will be closed on:<span class="Apple-converted-space"> </span><br>> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> Bart Pietercil<span class="Apple-converted-space"> </span><br>> Sent with aMac<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> On 3 Jan 2014 at 23:55:08, william humphrey (bill@bluewatermaritime.com)<span class="Apple-converted-space"> </span><br>> wrote:<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> Bart<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> Thank you for the clear description. I'm still trying to decide which<span class="Apple-converted-space"> </span><br>> would be more useful for me. There are like thirty vessels and each<span class="Apple-converted-space"> </span><br>> new voyage will be for one of the vessels. Only one vessel in each new<span class="Apple-converted-space"> </span><br>> voyage. But if I just auto increment for voyages it is not as useful<span class="Apple-converted-space"> </span><br>> as auto increment that is unique only to each vessel for that voyage.<span class="Apple-converted-space"> </span><br>> That would mean the field would not be a unique value for the voyages<span class="Apple-converted-space"> </span><br>> as it would only be the combination of vessel number and voyage number<span class="Apple-converted-space"> </span><br>> only that is unique.<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> Bill<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> On Fri, Jan 3, 2014 at 5:56 PM, Bart Pietercil<span class="Apple-converted-space"> </span><br>> <bart.pietercil@cognosis.be> wrote:<span class="Apple-converted-space"> </span><br>>> Theoretically you can. What I don’t know is whether the Valentina Engine<span class="Apple-converted-space"> </span><br>>> already supports it.<span class="Apple-converted-space"> </span><br>>> The sequence generator has been added to vStudio in order to support the<span class="Apple-converted-space"> </span><br>>> Postgresql engine.<span class="Apple-converted-space"> </span><br>>> In Postgresql you would do<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> add field vessel_nr serial (or big serial).<span class="Apple-converted-space"> </span><br>>> This is shorthand for<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> add field vessel_nr integer (or bigint) unique<span class="Apple-converted-space"> </span><br>>> create sequence seq_vessel_nr<span class="Apple-converted-space"> </span><br>>> set default value of field vessel_nr to nextval(seq_vessel_nr)<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> so now you have a field vessel_nr with an autoincrementing value on every<span class="Apple-converted-space"> </span><br>>> insert (creation of vessel trip?)<span class="Apple-converted-space"> </span><br>>> the only thing left to do is , if you already have data in your vessel<span class="Apple-converted-space"> </span><br>>> table, generate numbers for the existing values.<span class="Apple-converted-space"> </span><br>>> And the last thing to do is set the startvalue of seq_vessel_nr to<span class="Apple-converted-space"> </span><br>>> max(vessel_nr)+1.<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> From hereon your vessel_nr will autoincrement with the correct number<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> hth<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> bart<span class="Apple-converted-space"> </span><br>>> —<span class="Apple-converted-space"> </span><br>>> Our office will be closed on:<span class="Apple-converted-space"> </span><br>>> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> Bart Pietercil<span class="Apple-converted-space"> </span><br>>> Sent with aMac<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> On 3 Jan 2014 at 16:47:59, william humphrey (bill@bluewatermaritime.com)<span class="Apple-converted-space"> </span><br>>> wrote:<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> Can that kind of create sequence be done after the fact of table<span class="Apple-converted-space"> </span><br>>> finished and data already in?<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> On Fri, Jan 3, 2014 at 11:23 AM, Ruslan Zasukhin<span class="Apple-converted-space"> </span><br>>> <ruslan_zasukhin@valentina-db.com> wrote:<span class="Apple-converted-space"> </span><br>>>> On 1/3/14, 4:46 PM, "william humphrey" <bill@bluewatermaritime.com><span class="Apple-converted-space"> </span><br>>>> wrote:<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>>> I've been thinking that a sequence maintained with a trigger might be<span class="Apple-converted-space"> </span><br>>>>> very useful for other things too. I never thought of numbering<span class="Apple-converted-space"> </span><br>>>>> sequentially the voyages of each vessel. It solves some other<span class="Apple-converted-space"> </span><br>>>>> problems. Right now voyage numbers increment from zero at the start of<span class="Apple-converted-space"> </span><br>>>>> each new year (which is what caused the problem) but there is no<span class="Apple-converted-space"> </span><br>>>>> reason why I can't have an extra field which has every voyage for that<span class="Apple-converted-space"> </span><br>>>>> vessel down through the years.<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> Do you talk about CERATE SEQUENCE?<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> For simple case, consider SERIAL32 or SERIAL64 pseudo-types,<span class="Apple-converted-space"> </span><br>>>> As even more simple things.<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> --<span class="Apple-converted-space"> </span><br>>>> Best regards,<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> Ruslan Zasukhin<span class="Apple-converted-space"> </span><br>>>> VP Engineering and New Technology<span class="Apple-converted-space"> </span><br>>>> Paradigma Software, Inc<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> Valentina - Joining Worlds of Information<span class="Apple-converted-space"> </span><br>>>> http://www.paradigmasoft.com<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> [I feel the need: the need for speed]<span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>><span class="Apple-converted-space"> </span><br>>>> _______________________________________________<span class="Apple-converted-space"> </span><br>>>> Valentina mailing list<span class="Apple-converted-space"> </span><br>>>> Valentina@lists.macserve.net<span class="Apple-converted-space"> </span><br>>>> http://lists.macserve.net/mailman/listinfo/valentina<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> --<span class="Apple-converted-space"> </span><br>>> http://www.bluewatermaritime.com<span class="Apple-converted-space"> </span><br>>> _______________________________________________<span class="Apple-converted-space"> </span><br>>> Valentina mailing list<span class="Apple-converted-space"> </span><br>>> Valentina@lists.macserve.net<span class="Apple-converted-space"> </span><br>>> http://lists.macserve.net/mailman/listinfo/valentina<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>>> _______________________________________________<span class="Apple-converted-space"> </span><br>>> Valentina mailing list<span class="Apple-converted-space"> </span><br>>> Valentina@lists.macserve.net<span class="Apple-converted-space"> </span><br>>> http://lists.macserve.net/mailman/listinfo/valentina<span class="Apple-converted-space"> </span><br>>><span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> --<span class="Apple-converted-space"> </span><br>> http://www.bluewatermaritime.com<span class="Apple-converted-space"> </span><br>> _______________________________________________<span class="Apple-converted-space"> </span><br>> Valentina mailing list<span class="Apple-converted-space"> </span><br>> Valentina@lists.macserve.net<span class="Apple-converted-space"> </span><br>> http://lists.macserve.net/mailman/listinfo/valentina<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br>> _______________________________________________<span class="Apple-converted-space"> </span><br>> Valentina mailing list<span class="Apple-converted-space"> </span><br>> Valentina@lists.macserve.net<span class="Apple-converted-space"> </span><br>> http://lists.macserve.net/mailman/listinfo/valentina<span class="Apple-converted-space"> </span><br>><span class="Apple-converted-space"> </span><br><br><br><br>--<span class="Apple-converted-space"> </span><br>http://www.bluewatermaritime.com<span class="Apple-converted-space"> </span><br>_______________________________________________<span class="Apple-converted-space"> </span><br>Valentina mailing list<span class="Apple-converted-space"> </span><br>Valentina@lists.macserve.net<span class="Apple-converted-space"> </span><br>http://lists.macserve.net/mailman/listinfo/valentina<span class="Apple-converted-space"> </span><br></div></div></span></blockquote></div></body></html>