SQL help

[william humphrey]

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