Please help me about sql query?

Hi, everyone

I have a data set following:
Datetime Name Value
2006-10-10 13:30:00 AM A 2
2006-10-10 13:30:00 AM B 1
2006-10-10 13:30:00 AM C 5
2006-10-10 14:30:00 AM A 6
2006-10-10 14:30:00 AM B 3
2006-10-10 14:30:00 AM C 1
2006-10-10 15:30:00 AM A 0
2006-10-10 15:30:00 AM B 8
2006-10-10 15:30:00 AM C 4
2006-10-10 16:30:00 AM A 2
2006-10-10 16:30:00 AM B 4
2006-10-10 16:30:00 AM C 3
..................

Now, I want to the result that A, B, C are max of fields:
2006-10-10 14:30:00 AM A 6
2006-10-10 15:30:00 AM B 8
2006-10-10 13:30:00 AM C 5

How to write sql query to return this result?
Please help me!

Thanks for your advise

Best regard,

assume the following table structure...

create table test1
(
dt datetime,
name varchar(10),
num int
)

This is the query for ur problem...
select *
from test1 a
where num = (select max(num) from test1 b where b.name = a.name)
order by name

Thanks very much

That's a good idea. I's apply to my database, but it have a problem.

If I have 12 tables are the same structure, every table contain data for a month. If I use this query:

SELECT * FROM (

select *
from test1_200601 a
where num = (select max(num) from test1_200601 b where b.name = a.name)
order by name

UNION

select *
from test1_200602 a
where num = (select max(num) from test1_200602 b where b.name = a.name)
order by name

UNION

select *
from test1_200603 a
where num = (select max(num) from test1_200603 b where b.name = a.name)
order by name

....


UNION

select *
from test1_200612 a
where num = (select max(num) from test1_200612 b where b.name = a.name)
order by name
) AS AA

ORDER BY name

Results:

2006-01-10 13:30:00 AM A 2
2006-02-04 13:30:00 AM B 1
2006-03-12 13:30:00 AM C 5
2006-04-10 14:30:00 AM A 6
2006-05-10 14:30:00 AM B 3
2006-06-19 14:30:00 AM C 1
2006-07-10 15:30:00 AM A 0
2006-08-10 15:30:00 AM B 8
2006-09-23 15:30:00 AM C 4
2006-10-10 16:30:00 AM A 2
2006-11-04 16:30:00 AM B 4
2006-12-10 16:30:00 AM C 3

Return results is a table that have many record is the same info but value is difference (Each max record is gotten from 1 table).

I want results are following:

2006-04-10 14:30:00 AM A 6
2006-08-10 15:30:00 AM B 8
2006-03-12 13:30:00 AM C 5

But I can't use your technology for this case. How to select max record from this table?

Thanks