[RESOLVED] which fields get selected when you use a GROUP BY clause?

[sneakyimp]

Suppose I'm selecting from some tables using a GROUP BY clause:

SELECT brand, model, year, color FROM cars
WHERE cost > 2000
AND cost < 500000
GROUP BY brand

Suppose further that my 'cars' table has 20 different records with 'aston martin' as their brand but with varying model, year, and colors. Given that this query returns only one record for each brand, what values will show up in model, year, and color?

Also, what happens when I am joining multiple tables with a GROUP BY clause...like if I had multiple drivers per car. Whose name would show up in this query:

SELECT c.brand, c.model, c.year, c.color, d.name
FROM cars c, drivers d
WHERE d.car_id=c.id
AND c.cost > 2000
AND c.cost < 500000
GROUP BY c.brand

[Piranha]

First I have to say that GROUP BY is normally not used this way. It is normally used when using COUNT, SUM and other such functions. Like the example below that returns the id and name and how many cars each driver have:

SELECT d.id, d.name, COUNT(c.id)
FROM drivers d, cars c
WHERE d.id = c.driverid
GROUP BY d.id

Back to your actual question. The answer to both the questions is that there is no rule for what will be returned. Normally it will, at least in the first query, return the row that was inserted first into the database. I don't know what driver is returned in the second query, but it is probably the driver that was first inserted in the database. But as I said before you can't trust that it will return that value, so I advice you not to use GROUP BY this way.

[sneakyimp]

Yeah I think you're right. It's doing exactly what I want, which is just pick the first or one at random, but I don't want any unpredictable behavior going on in my application.