Help with MAX()

Hi,
Need a query help.
Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No
Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.
How do I do it?Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

I think that should be it ...|||Sorry. That doesn't seem to give me the result I want.

These are my data.

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372873','2005-10-10','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372874','2005-10-11','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372872','2005-10-12','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372875','2005-10-13','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372876','2005-10-14','Approved');

I just want the latest records for each Serial_No. In this case, I would want
the third and the fifth record.|||select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

SELECT *
FROM Logs INNER JOIN
(SELECT Serial_No, MAX(Log_Time) AS MaxOfLogs
FROM logs
GROUP BY Serial_No) LogMaxTime ON
Logs.Serial_No = LogMaxTime.Serial_No
AND Logs.Log_Time = LogMaxTime.MaxOfLogs :)|||It WORKS!!!
Thanks a lot!! :)|||Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

or

Select Serial_No, Log_Time , * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

I think second is the best to use. Because it is Optimised.

Regards,
Subramanyam.|||the second is not the best to use, because it is invalid syntax

:)|||...but other than that minor drawback, it is way ultra-cool.