|
|
Posted by Bobbo on 08/23/06 08:49
Skip wrote:
> I need to get the max row date from the following query. There is a
> date field in rcompl.date_time. There can be several rows identical
> except the date_time in which I need only the max of those rows. Thanks
>
> select rcompl.*,fastener_database.dbo.fastener_db_working.*
> FROM Rcompl LEFT OUTER JOIN fastener_database.dbo.fastener_db_working
> ON
> substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) =
> fastener_db_working.[Serial Number]
> where len(hole_name)>2 and status<>'5' and program_id in (select
> program_id from nc_programs where job_id =@job_id)
I'm assuming here that you've got a series of fields which are common,
with a varying date field.
Try removing the * field selectors and explicitly state the fields you
need -- this is good practice anyway. Then GROUP BY the other fields
and return the max(rcompl.date_time).
So, in a more simplistic example, you might have:
SELECT Title, Author, max(LastSale) as LastSale
FROM tbl_BookSales
GROUP BY Title, Author
This might return a list of books with their most recent sale date,
from a book shop database.
Get it working with a simple table like this, before you implement it
in your more complex query above. And don't use those SELECT *s!
Navigation:
[Reply to this message]
|