Trying to retrieve most recent record per date

[Jonathan]

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-27','11:26:00'
'50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','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','14: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','15: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','15: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-27','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

[strawberry]

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-27','11:26:00'
'50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','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','14: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','15: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','15: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-27','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