Trying to retrieve most recent record per date

I have the following query which is retrieving a set of data it is
almost what I want but I can not manage to get the result I desire.

SELECT r1, r1_dev, r2, r2_dev, date, time
FROM output
WHERE output.id_modality = '1' AND output.id_linac='2'
ORDER BY output.date DESC

'r1','r1_dev','r2','r2_dev','date','time'
'37.500','0.334449476181','[NULL]','[NULL]','2006-07-27','15 :00:00'
'50.000','0.334449476181','37.470','0.0000364374','2006-07-2 7','11:26:00'
'50.000','0.334449476181','37.470','0.0000364374','2006-07-2 7','11:26:00'
'40.812','0.8538683','0.000','0.0000000000','2006-05-12','08 :50:00'
'41.580','2.7517359','40.370','-0.2383940000','2006-05-12',' 08:45:00'
'40.756','0.8155671','0.000','0.0000000000','2006-05-12','15 :00:00'
'37.952','-0.472445','0.000','0.0000000000','2006-05-12','14 :53:18'
'38.010','-0.3203424','0.000','0.0000000000','2006-05-12','1 4:52:33'
'39.488','3.3474615','37.916','-0.7667557000','2006-05-12',' 08:35:43'
'39.650','3.7714458','38.020','-0.4945683000','2006-05-12',' 08:22:38'
'40.330','-0.2382025','0.000','0.0000000000','2006-05-12','1 5:00:00'
'41.330','2.2354728','0.000','0.0000000000','2006-05-05','00 :00:00'
'39.220','2.8528707','0.000','0.0000000000','2006-05-05','00 :00:00'
'40.696','0.8814325','0.000','0.0000000000','2006-04-28','15 :56:00'
'41.000','1.635019','0.000','0.0000000000','2006-04-28','15: 55:00'
'38.380','0.8642144','38.400','0.9167752000','2006-04-28','1 5:56:10'
'37.970','-0.2132824','38.510','1.2058597000','2006-04-28',' 15:49:23'

From the set above I would like to retrieve the latest measurement per
date but if I try to group by date (like below) I always get the first
result instead of the latest result.

SELECT r1, r1_dev, r2, r2_dev, date, time
FROM output
WHERE output.id_modality = '1' AND output.id_linac='2'
GROUP BY output.date ASC
ORDER BY output.date DESC

'r1','r1_dev','r2','r2_dev','date','time'
'50.000','0.334449476181','37.470','0.0000364374','2006-07-2 7','11:26:00'
'39.650','3.7714458','38.020','-0.4945683000','2006-05-12',' 08:22:38'
'39.220','2.8528707','0.000','0.0000000000','2006-05-05','00 :00:00'
'37.970','-0.2132824','38.510','1.2058597000','2006-04-28',' 15:49:23'

Who can help me solve my problem?

Thanks in advance!

Jonathan

Re: Trying to retrieve most recent record per date

Jonathan wrote:
> I have the following query which is retrieving a set of data it is
> almost what I want but I can not manage to get the result I desire.
>
> SELECT r1, r1_dev, r2, r2_dev, date, time
> FROM output
> WHERE output.id_modality = '1' AND output.id_linac='2'
> ORDER BY output.date DESC
>
> 'r1','r1_dev','r2','r2_dev','date','time'
> '37.500','0.334449476181','[NULL]','[NULL]','2006-07-27','15 :00:00'
> '50.000','0.334449476181','37.470','0.0000364374','2006-07-2 7','11:26:00'
> '50.000','0.334449476181','37.470','0.0000364374','2006-07-2 7','11:26:00'
> '40.812','0.8538683','0.000','0.0000000000','2006-05-12','08 :50:00'
> '41.580','2.7517359','40.370','-0.2383940000','2006-05-12',' 08:45:00'
> '40.756','0.8155671','0.000','0.0000000000','2006-05-12','15 :00:00'
> '37.952','-0.472445','0.000','0.0000000000','2006-05-12','14 :53:18'
> '38.010','-0.3203424','0.000','0.0000000000','2006-05-12','1 4:52:33'
> '39.488','3.3474615','37.916','-0.7667557000','2006-05-12',' 08:35:43'
> '39.650','3.7714458','38.020','-0.4945683000','2006-05-12',' 08:22:38'
> '40.330','-0.2382025','0.000','0.0000000000','2006-05-12','1 5:00:00'
> '41.330','2.2354728','0.000','0.0000000000','2006-05-05','00 :00:00'
> '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00 :00:00'
> '40.696','0.8814325','0.000','0.0000000000','2006-04-28','15 :56:00'
> '41.000','1.635019','0.000','0.0000000000','2006-04-28','15: 55:00'
> '38.380','0.8642144','38.400','0.9167752000','2006-04-28','1 5:56:10'
> '37.970','-0.2132824','38.510','1.2058597000','2006-04-28',' 15:49:23'
>
> From the set above I would like to retrieve the latest measurement per
> date but if I try to group by date (like below) I always get the first
> result instead of the latest result.
>
> SELECT r1, r1_dev, r2, r2_dev, date, time
> FROM output
> WHERE output.id_modality = '1' AND output.id_linac='2'
> GROUP BY output.date ASC
> ORDER BY output.date DESC
>
> 'r1','r1_dev','r2','r2_dev','date','time'
> '50.000','0.334449476181','37.470','0.0000364374','2006-07-2 7','11:26:00'
> '39.650','3.7714458','38.020','-0.4945683000','2006-05-12',' 08:22:38'
> '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00 :00:00'
> '37.970','-0.2132824','38.510','1.2058597000','2006-04-28',' 15:49:23'
>
> Who can help me solve my problem?
>
> Thanks in advance!
>
> Jonathan

SELECT o1 . *
FROM output o1
LEFT JOIN output o2 ON o2.id <> o1.id
AND o2.date = o1.date
AND o2.time > o1.time
WHERE ISNULL( o2.time )
LIMIT 0 , 30

In your dataset, two of the latest results occur at the time on the
same day. This means that they will both be returned - unless you
specify some further criteria