Selecting from groups

Why don't I get the rows with the groupwise maximum?

Your query might look like this:

SELECT col1, MAX(col2), col3 FROM table GROUP BY col1;

Sometimes you get the value of col3 you expect, but usually you don't. Why not?

Imagine a table containing some shapes of various sizes and colors. You can put them in groups by color:

SELECT * FROM figures GROUP BY color;

http://www.thenoyes.com/storage/color.png

What results do you expect if you do:

SELECT shape FROM figures GROUP BY color;

Perhaps the green group will return "square". Perhaps "circle". There's no telling.

Now, what if we ran:

SELECT shape, MAX(diameter) FROM figures GROUP BY color;

You could predict the returned value for the maximum diameter for each group. Clearly that green square is bigger than the other shapes. But the fields in the SELECT list are independent. You could still get a 'circle' value for the shape, even though the diameter is from the square.