Thanks for the question, Joshua .
Asked: September 28, 2007 - 5:23 pm UTC
Last updated: October 01, 2007 - 7:57 am UTC
Version: 8.1.7
Viewed 1000+ times
You Asked
I have this select
Select
Jobstop.PODdatetime Date_Time,
job.routenumber Route,
Stop.Name Location,
Jobstop.PODName,
Case When Jobstop.PODSign IS NOT NULL Then 'Electronic' End AS PODsign,
JobStop.ArriveDateTimeSource ArriveScan,
Piece.Reference
From
Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted) on Customer.CustomerID=Ordermain.CustomerID
Left Outer Join Job with (readuncommitted) on Ordermain.OrderID=Job.OrderID
Left Outer Join Jobstop with (readuncommitted) on Job.JobID=Jobstop.JobID
Left Outer Join Stop with (readuncommitted) on Jobstop.StopID=Stop.StopID
Left Outer Join Jobstoppiece with (readuncommitted) on Jobstop.JobstopID=Jobstoppiece.JobstopID
Left Outer Join Piece with (readuncommitted) on Jobstoppiece.PieceID=Piece.PieceID
Where
Ordermain.Orderstatus IN ('N', 'A', 'I','P') And
Left(Ordermain.Service,3) NOT IN ('LEA', 'FUE', 'Wai', 'Mon') and
Ordermain.Orderdate between '9/21/07' AND '9/27/07'
and left(piece.reference,2)='TL'
and customer.customercode='331'
Order By
Piece.reference, JobStop.PODdatetime
AND IT RETURNS THIS TYPE OF SET
Date Route Location Name Reference#
9/27 1 A bob 333
9/15 1 A jim 333
9/17 3 b jim 222
9/15 7 D sam 222
I only want one row per reference number and I want to one with the most recent date.
Can I do this?
and Tom said...
well, since Oracle finds readuncommitted to be something to be fearful of and has not any use for....
and since hints work entirely differently in Oracle than in SQLServer
I truly suggest you find an "askmicrosoft" forum, so you can get a good answer for the database you are working with.
I can say however, the outer join to piece and customer - utterly not useful. Think about it - if you outer join to them - AND a row was made up for them, how could the customercode (which would be NULL in the outer joined row) be 331 or the reference (which would be NULL) be like 'TL%'??
And if stop outer joining to customer and piece - off hand - I'd say the rest of the outer joins are wasteful as well - if you make up a row in job, you'd make up on in jobstop, stop, jobstopiece and piece - but we know if you make up piece - you'd never select it....
Is this answer out of date? If it is, please let us know via a Comment