Fwd: SQL help

[william humphrey]

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