[thelist] MySQL Trouble: GROUP BY clauses

[Andrew Forsberg]

Hi All,

I've hit a bit of a brick wall. The following query is used in an INSERT
query, and does what it's supposed to do:

SELECT rate.rate_type_id, MIN(rate.rate)
FROM rate
WHERE (rate.rate != 0)
GROUP BY rate.rate_type_id
ORDER BY rate.rate_type_id

i.e., grab the lowest non-zero rate for each rate type, and order it by
the rate type. The problem is, I'd really like to be able to grab the
rate.id (well, another column, but that's not the problem), but I can't
find the correct combination of WHERE and GROUP BY clauses to get it.
For example:

SELECT rate.rate_type_id, rate.id, MIN(rate.rate)
FROM rate
WHERE (rate.rate != 0)
GROUP BY rate.rate_type_id
ORDER BY rate.rate_type_id

Gives me the correct minimum rate, but seemingly random rate ids... I
can't quite put my finger on what I'm doing wrong, can anyone help
please?

Thanks,
Andrew