-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE data (id INTEGER NOT NULL PRIMARY KEY, dato DATETIME, val INTEGER);

INSERT INTO data VALUES(1,'2008-12-01 12:00:00', 6);
INSERT INTO data VALUES(2,'2008-12-01 13:00:00', 3);
INSERT INTO data VALUES(3,'2008-12-02 07:00:00', 8);
INSERT INTO data VALUES(4,'2008-12-02 12:30:00', 5);
INSERT INTO data VALUES(5,'2008-12-02 13:30:00', 8);

SELECT DATE_FORMAT(dato,'%Y-%m-%d'),MAX(val)
FROM data
GROUP BY DATE_FORMAT(dato,'%Y-%m-%d');

SELECT *
FROM data a
WHERE NOT EXISTS (SELECT *
                  FROM data b
                  WHERE DATE_FORMAT(b.dato,'%Y-%m-%d') = DATE_FORMAT(a.dato,'%Y-%m-%d')
                  AND (b.val > a.val OR (b.val = a.val AND b.dato < a.dato)));

DROP TABLE data;
