Graham Roberts wrote:
> Also, can you confirm whether the GROUP BY solution I'm using is guaranteed to always use
> details from the first record in the group, rather than it being a case of random selection.
I've said it many times and I say it again. There is no "first record" in a table, since a table is a set.
You can force a first record by using ORDER BY something, but not in a GROUP BY group (unless you fool with the non-standard GROUP_CONCAT() function).
To answer your question: most of the time, but not always. Run the following code to see what I mean:
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c CHAR(10) NOT NULL,
p DECIMAL(10,2) NOT NULL,
d DATE NOT NULL
) ENGINE=MyISAM;
INSERT INTO foo (c, p, d) VALUES
('X', 0.00, '2000-01-01'),
('A', 10.00, '2005-01-01'),
('A', 15.00, '2005-01-02'),
('A', 20.00, '2005-01-03'),
('B', 30.00, '2005-02-01'),
('B', 40.00, '2005-02-02');
SELECT * FROM foo;
SELECT
c, MAX(p), d
FROM foo
GROUP BY c;
DELETE FROM foo WHERE id = 1;
INSERT INTO foo (c, p, d) VALUES ('A', 50.00, '2005-08-09');
SELECT * FROM foo;
SELECT
c, MAX(p), d
FROM foo
GROUP BY c;
--
felix
Please use
BBCode to format your messages in this forum.