Greg Stark <[EMAIL PROTECTED]> writes: > I think most MySQL users don't stumble on it, they learn it as the way > to handle the common use case when you join a master table against a > detail table and then want to aggregate all the detail records. In > standard SQL you have to write GROUP BY ... and list every single > column you need from the master table. Forcing the database to do a > lot of redundant comparisons and sort on uselessly long keys where in > fact you only really need it to sort and group by the primary key.
Actually, if you're grouping by a table's primary key, the SQL99 spec says you shouldn't have to explicitly list the other columns from that table --- they are guaranteed to have unique values per group anyway. This is a single case in the "functional dependency" stuff. That verbiage is incredibly dense and I don't think we want to tackle all of it any time soon, but the primary-key case probably wouldn't be very hard to implement. We really ought to have this in TODO ... I'm sure it's been discussed before. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match