MySQL Forums
Forum List  »  Newbie

Re: Using ORDER BY and GROUP BY together
Posted by: Felix Geerinckx
Date: August 10, 2005 11:20AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: Using ORDER BY and GROUP BY together
August 10, 2005 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.