Max Row Date

[Skip]

Hello,
I am reposting this from another group, because I had no responses.

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)

[Bobbo]

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!

[Skip]

Bobbo,
This has helped a little. Below is a
sample of data, problem is that the 'duplicate data' is for the
hole_name field only. But I need to include all the data. I have
simplified the query for the example.

Sample Data:
id hole_name Mtime
156140 2a000020x1 2006-08-02 19:18:34.000
156141 2a000021x1 2006-08-02 19:19:45.000
156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this
record
156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only
156144 2a000023x1 2006-08-02 19:29:53.000
This works but I cannot include any additional fields:
select hole_name,max(date_time)as Mtime
FROM Rcompl
where program_id in (select program_id from nc_programs where job_id
='156')
group by hole_name
Result:
hole_name Mtime
2a000020x1 2006-08-02 19:18:34.000
2a000021x1 2006-08-02 19:19:45.000
2a000022x1 2006-08-02 19:29:18.000
2a000023x1 2006-08-02 19:29:53.000
So how do I get this to work with all the 28 fields.
Be glad to provide more info. Thanks for the help.

Bobbo wrote:

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!

[Bobbo]

Skip wrote:

Sample Data:
id hole_name Mtime
156140 2a000020x1 2006-08-02 19:18:34.000
156141 2a000021x1 2006-08-02 19:19:45.000
156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this
record
156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only
156144 2a000023x1 2006-08-02 19:29:53.000

This works but I cannot include any additional fields:

Ah, I get you. Try doing the max() and GROUP thing as a subquery and
link it back out to your larger result set, something like this:

select r1.[id], r1.hole_name, r1.mtime, etc...
from rcompl r1
where mtime = (select max(mtime) from rcompl r2 where r1.hole_name =
r2.hole_name)

Or alternatively this (someone else might be able to advise which of
these is better):

select r1.[id], r1.hole_name, r1.mtime, etc...
from rcompl r1
join (select hole_name, max(mtime) from rcompl group by hole_name) r2
on (r1.hole_name = r2.hole_name and r1.mtime = r2.mtime)

[Skip]

Thanks! Works great

Bobbo wrote:

Skip wrote:

Sample Data:
id hole_name Mtime
156140 2a000020x1 2006-08-02 19:18:34.000
156141 2a000021x1 2006-08-02 19:19:45.000
156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this
record
156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only
156144 2a000023x1 2006-08-02 19:29:53.000

This works but I cannot include any additional fields:

Ah, I get you. Try doing the max() and GROUP thing as a subquery and
link it back out to your larger result set, something like this:

select r1.[id], r1.hole_name, r1.mtime, etc...
from rcompl r1
where mtime = (select max(mtime) from rcompl r2 where r1.hole_name =
r2.hole_name)

Or alternatively this (someone else might be able to advise which of
these is better):

select r1.[id], r1.hole_name, r1.mtime, etc...
from rcompl r1
join (select hole_name, max(mtime) from rcompl group by hole_name) r2
on (r1.hole_name = r2.hole_name and r1.mtime = r2.mtime)

[jsfromynr]

Hi Skip,
You may like to try this

Select * from YourTable A
Where Not Exists(Select 1 from YourTable B where A.hole_id=B.hole_id
and A.date_time>B.date_time)

Returning those rows of hole_id which have only max date_time.

I hope this helps

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com