Fwd: SQL help

[Bart Pietercil]

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  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20140103/bf607d09/attachment.html>