Fwd: SQL help

[Danny Lewkin]

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
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20140103/9d5dffc0/attachment-0001.html>