Hi,

Can anyone help me on this?
I have a table with the following fields: ID (unique), bID, type, value and createddate

the data type are uniqueidentifier, uniqueidentifier, varchar, ntext and datetime respectively.

sample data:
ID bID type value createddate
ui1 ui0 field1 value1 2006-02-13 16:39:21.100
ui2 ui0 field1 value2 2006-02-20 18:00:00.100
ui3 ui0 field2 value3 2006-02-13 16:39:21.200
ui4 ui0 field2 value4 2006-02-20 18:00:00.200

I'll need to retrieve the latest value record for each type.

Hence the expected result should be like this:
ID bID type value createddate
ui2 ui0 field1 value2 2006-02-20 18:00:00.100
ui4 ui0 field2 value4 2006-02-20 18:00:00.200

I am aware that since the datatype value is ntext, i cannot do comparison of that field together with the max() function.

I hope to carry out the query in a single query instead of separate 2 queries that I thought of.

Pls advise. thxs in advance!

Try:

select a.ID, a.bID, a.type, a.value, a.createddate
from aaaa A
inner join
( select type, max(createddate) as maxdate
    from aaaa
    group by type
) B on A.type=B.type and A.createddate=B.maxDate

Jim

The routine as listed works for a similar problem i have, except I need to add a limiting date parameter. ex: exclude records with a date > date_parameter and then take the last record.

Is this possible?

Thanks,
Dan