|
Subject:
|
SQL query retrieving last record and group by
|
|
Posted By:
|
snowy
|
Post Date:
|
3/16/2006 5:14:29 AM
|
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!
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
3/20/2006 12:13:43 AM
|
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
|
|
Reply By:
|
danprox
|
Reply Date:
|
12/13/2006 12:59:41 PM
|
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
|
