View Single Post
 
Old March 16th, 2006, 06:14 AM
snowy snowy is offline
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL query retrieving last record and group by

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!