Joshua -- Thanks for the question regarding "Select entire rows with most recent date only. No duplicates", version 8.1.7

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 we 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....

---

Write a Review