|
|
Posted by Skip on 08/23/06 13:42
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!
Navigation:
[Reply to this message]
|